Consecutive values; Averages for a single month.

[emiguerra]

Right off the bat I want to thank everyone for their help in these
boards. It's an amazing resource. I have a big spreadsheet with daily
values over time.

I want to see for how many CONSECUTIVE days the value was over 90, and
also to get the range where that happened. So if I get a maximum value
for the consecutive days, can I also get the range of where that
happened in the spreadsheet?

Also, Is there an easy way to get, for example, the averages for a
month. I have the values in both MM/DD/YY in a single cell and also
one individual column for each month day and year (so 06/26/1968) and
one column each for 6, 26 and 1968, respectively. The values are in a
separate column.

Many thanks!

 

[Paul Hyett]

Right off the bat I want to thank everyone for their help in these
boards. It's an amazing resource. I have a big spreadsheet with daily
values over time.

I want to see for how many CONSECUTIVE days the value was over 90, and
also to get the range where that happened. So if I get a maximum value
for the consecutive days, can I also get the range of where that
happened in the spreadsheet?

Would those be temperatures, by any chance?

If so, any such formula would be useful to me, too.

 

[Ms-Exl-Learner]

Assume that your dates are in Column A.

Column-A
Dates
20-Nov-10
21-Nov-10
22-Nov-10
23-Nov-10
24-Sep-10
25-Sep-10
26-Sep-10
27-Sep-10

1st row is having the headers of the column that is Dates. So your
data start from 2nd Row.

Copy and paste the below formula in B2 cell.
=IF(A2="","",IF(DATEDIF(TODAY(),A2,"D")>90,"Greater Than 90 Days",""))
Drag the B2 cell formula to the remaining cells of B Column based on
the Column-A data.
The above formula will compare the Column a date with todays date and
show message in the cell like “Greater Than 90 Days”. If it is less
than 90 days then it will keep showing blank.

Instead of mentioning the Today as the date criteria and if you want
to fix a specific date to compare then remove the Today function from
the above formula and mention the date using the Date function like
the below.
=IF(A2="","",IF(DATEDIF(DATE(2010,7,20),A2,"D")>90,"Greater Than 90
Days",""))
In the above formula I have mentioned the date as 20/07/2010 using the
Date function DATE(2010,7,20). Change the date 20/07/2010 to your
desired date if required.

After applying the B2 formula to the whole column of B based on the A
column data apply the Filter for Column B and select “Greater Than 90
Days”.

Change the cell reference A2 to your desired cell, if required.

Hope it’s clear to you!