• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need your help to build a function in Excel!

lokuwel

lokuwel

New Member
Hi
I need to create a function in an Excel sheet based on the following

There I want to find the Number of hits based on the following conditions


"No of Hits" - When ('Score' < "Threshold" & 'speed" !=0) < 'Constant Time' in minutes before 'Uptime = 0"

The make it clear you need to look at the time when "Uptime" = 0.
Let's say the time was 12:00. If 'Constant Time' == 120 minutes. So our interval is between 10:00 till 12:00.
You take the number of times when ('Score' < "Threshold" & 'speed" !=0) between 12:00 till 10:00.
This is will make the 'No of Hits'.

Threshold is a default value 20%
Constant Time is 120 Min It is fixed value.

I have attached sample excel sheet for you better understanding.Please help!

Best Regards
Franz
 

Attachments

  • test 2.xlsx
    8.9 KB · Views: 3
Last edited:
Hi ,

Can you upload a workbook which has enough data , say a few hundred rows , so that the time span of 120 minutes and more is covered ?

Narayan
 
NARAYANK991 said:
Hi ,

Can you upload a workbook which has enough data , say a few hundred rows , so that the time span of 120 minutes and more is covered ?

Narayan
Click to expand...
Here is my entire worksheet.Please find the attachment.Thank you.
 

Attachments

  • aaaaaa.xlsx
    941.2 KB · Views: 4
Hi ,

Thanks for the upload.

I assume that we need to look at only columns A , B , C and D.

First , the times in column A are in descending order ; can we sort the data so that these times are in ascending order ?

Narayan
 
NARAYANK991 said:
Hi ,

Thanks for the upload.

I assume that we need to look at only columns A , B , C and D.

First , the times in column A are in descending order ; can we sort the data so that these times are in ascending order ?

Narayan
Click to expand...
Yes,Columns are A,B,C and D.
I arranged the data according to the ascending order and reattached the excel workbook.Please find the attachment.Thank you!
 
Hi ,

Where is the attachment please ? I downloaded the most recent upload , and the data appears to be in descending order , from the latest date and time downwards to the earliest date and time.

Anyway , can I sort the data in ascending order , from the earliest date and time to the latest date and time ?

Narayan
 
NARAYANK991 said:
Hi ,

Where is the attachment please ? I downloaded the most recent upload , and the data appears to be in descending order , from the latest date and time downwards to the earliest date and time.

Anyway , can I sort the data in ascending order , from the earliest date and time to the latest date and time ?

Narayan
Click to expand...
I wasn't able to attached the document.I think it is due to a technical reason.Can you please send me your email then I can send the document.If you can solve this it is a great help to me. my email address is lokuwelasoka@gmail.com
 
Hi ,

I am uploading a part of your full workbook , with only the relevant 4 columns of data , sorted in ascending order on the date and time.

I have highlighted the start and end of each 120 minute interval ; can you confirm that this is correct ?

Narayan
 

Attachments

  • Book1 (13).xlsx
    29 KB · Views: 1
NARAYANK991 said:
Hi ,

I am uploading a part of your full workbook , with only the relevant 4 columns of data , sorted in ascending order on the date and time.

I have highlighted the start and end of each 120 minute interval ; can you confirm that this is correct ?

Narayan
Click to expand...
yes,it is correct.
 
Hi ,

If you confirm that these intervals have been marked correctly , then can we take the first interval and discuss what is required ?

The first interval is the period from 06.11.19 00:00 to 06.11.19 01:59.

In this interval if we see how many times the value in column B was less than Threshold , where Threshold is a named range defined as 20% , then the total number of hits = 29.

Can you confirm that this is correct ?

Narayan
 

Attachments

  • Book1 (13).xlsx
    29.8 KB · Views: 4
NARAYANK991 said:
Hi ,

If you confirm that these intervals have been marked correctly , then can we take the first interval and discuss what is required ?

The first interval is the period from 06.11.19 00:00 to 06.11.19 01:59.

In this interval if we see how many times the value in column B was less than Threshold , where Threshold is a named range defined as 20% , then the total number of hits = 29.

Can you confirm that this is correct ?
So the total condition is ;
When ('Score' < "Threshold" & 'speed" !=0) < 'Constant Time' in minutes before 'Uptime = 0"

Narayan
Click to expand...
Yes,It is correct.
 
Last edited:
You must log in or register to reply here.
Back
Top