Formula for comparing a date to today's date

[Guest]

I am in need of a formula to compare today's date to a previous date and
perform a calculation based on the number of days in between.

Oct 1 equipment ran 12 of 16 hours
Oct 2 equipment ran 16 of 16 hours
Oct 10 Equipment ran 10 of 16 hours

I need percentage of today's uptime with equipment assuming every day has 16
available hours. Oct 1 - Oct 10 has 160 available hours for
(12+16+...10)/160= 93.7% uptime. I want this to be dynamic so that at any
given date I can look at real time percentage.

 

[vezerid]

Hi,

If column A:A contains dates and column B:B contains uptime then

=AVERAGE(B:B)/16

should give you the uptime percentage, if you format the cell as %. It
will be dynamic, as it will change as more values are entered in B:B.
This will give you the total uptime since you started monitoring, so
naturally with time it should converge to your overall average.

Write back if you want something more specific, e.g. the average of the
last X days.

HTH
Kostis Vezerides

 

[Guest]

I am not familiar with the A:A, B:B notation. Is this an array form? Can I
look into a date in mid cycle to get the cummulative total to date?

 

[Roger Govier]

Hi

If you put the start date required in C1 and the end date in D1, then
enter in E1
=SUMPRODUCT((A1:A1000>=C1)*(A1:A1000<=D1)*(B1:B1000))/((D1-C1+1)*16)
Format E1 as Percentage
--
Regards

Roger Govier


"(e-mail address removed)"

 

[vezerid]

I am not familiar with the A:A, B:B notation. Is this an array form? Can I
look into a date in mid cycle to get the cummulative total to date?

A:A means the entire column A:A and it is (almost) identical to saying
A1:A65536. The formula
=AVERAGE(A:A)
will produce an average of all the numbers in column A. This is a good
technique when your column builds gradually and you want to monitor the
current average in another cell.

HTH
Kostis Vezerides