Count with conditions?

G

Geo

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.
 
J

Joel

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.
 
N

NDBC

This will only work if the dates in column a are consecutive (ie. all dates
are listed, even dates with 0 hours)

=if(and(max(b1:b7)>=1,sum(b1:b7)>=2),2,or(b7>=1,sum(b1:b7)>=2),1,"")

Assuming a seven day week as well.
 
D

David Biddulph

What does the FHOUR function do? Which version of Excel contains FHOUR?

The HOUR function would presumably give the same result as INT(MOD(A1,1)*24)
 
D

David Biddulph

I think the intention may have been
=IF(AND(MAX(B1:B7)>=1,SUM(B1:B7)>=2),2,IF(OR(B7>=1,SUM(B1:B7)>=2),1,""))
 
G

Geo

Thanks Dave, but still not working, I'll try explaining agian.
What I'm after is to count between any consecutive 7 day period any point
the time goes above 1 hour count as ("1"). Or if the cumulative total goes
over 2 hours count as ("1"). Hopefully this is made it clearer!!
 
N

NDBC

=IF(AND(MAX(B1:B7)>=1,SUM(B1:B7)>=2),2,IF(OR(Max(B1:B7)>=1,SUM(B1:B7)=2),1,""))

Put this in cell C7 (or whatever column you prefer) and copy/drag it down as
many rows as you have dates for. Again, this does not check any dates. It
relys on every date having a row, even weekends. If you are only putting in
dates that have times against them then this will not work.
 
G

Geo

I shall try it
Thank you NDBC

NDBC said:
=IF(AND(MAX(B1:B7)>=1,SUM(B1:B7)>=2),2,IF(OR(Max(B1:B7)>=1,SUM(B1:B7)=2),1,""))

Put this in cell C7 (or whatever column you prefer) and copy/drag it down as
many rows as you have dates for. Again, this does not check any dates. It
relys on every date having a row, even weekends. If you are only putting in
dates that have times against them then this will not work.
 
N

NDBC

Just hqad another thought. I've been assuming you've been putting in hours in
general format, not time format. Say for 1.5 hours do you put 1.5 hours in
column B or 1:30. If you are putting 1:30 then the formula I gave you won't
work.

try this

=IF(AND(MAX(B1:B7)*24>=1,SUM(B1:B7)*24>=2),2,IF(OR(Max(B1:B7)*24=1,SUM(B1:B7)*24=2),1,""))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top