Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
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) -- David Biddulph Joel wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
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,su m(b1:b7)=2),1,"") Assuming a seven day week as well. "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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
Sorry NDBC. I get an error message saying the formula you typed contains an
error? "NDBC" wrote: 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,su m(b1:b7)=2),1,"") Assuming a seven day week as well. "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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
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,"")) -- David Biddulph Geo wrote: Sorry NDBC. I get an error message saying the formula you typed contains an error? "NDBC" wrote: 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,su m(b1:b7)=2),1,"") Assuming a seven day week as well. "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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
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!! "David Biddulph" wrote: 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,"")) -- David Biddulph Geo wrote: Sorry NDBC. I get an error message saying the formula you typed contains an error? "NDBC" wrote: 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,su m(b1:b7)=2),1,"") Assuming a seven day week as well. "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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count with conditions?
=IF(AND(MAX(B1:B7)=1,SUM(B1:B7)=2),2,IF(OR(Max(B 1: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. "Geo" wrote: 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!! "David Biddulph" wrote: 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,"")) -- David Biddulph Geo wrote: Sorry NDBC. I get an error message saying the formula you typed contains an error? "NDBC" wrote: 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,su m(b1:b7)=2),1,"") Assuming a seven day week as well. "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT IF - 2 conditions | Excel Discussion (Misc queries) | |||
Count if - 2 conditions | Excel Discussion (Misc queries) | |||
How to count after 2 conditions are met | Excel Worksheet Functions | |||
Count ifs - 2 conditions | Excel Discussion (Misc queries) | |||
Count with 2 conditions? | Excel Worksheet Functions |