Count with conditions?
Excel stroes a day aas 1 and an hour as 1/24. To convert hours to whole
numbers simply multiply by 24. You may want to use the round function after
multiply to get an integer number of hours. You could also use the FHOUR()
function to get the number of hours insted of multiplying by 24. Hours will
alwasy give you an integer response but will only give you a number from 0 to
23.
"Geo" wrote:
How can I put the following as a formula:
1. 1 hour or greater to count as "1"
2. or a cumulative total of 2 hours (or more) in any seven consecutive days
to count as "1"
3. If the 1 hour and the 2 hours are exceeded within a consecutive seven day
period should count as "2"
The table is set out as follows:
cols: A= Date
B= Hours
Cells: "P5"= a consecutive 7 day period ("G5" and "H5"= dates for a rolling
last 7 days).
I have tried 'COUNT' but having trouble with it.
|