forumla that needs more.......

[numbers numbers]

I am looking for some help on a formula.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
purpose time keeping;<o></o>
User enters hours worked, (column B) program tracks hours worked in the last day, 30 days, 42 days and 90 days.<o></o>
<o> </o>
If the user has 5 days off in a row (column Q, zeros when column B has 5 days with zero hours) then the days tracked should reset to just that day and then start accumulating again.<o></o>
<o> </o>
current formula
=IF(Q32=0,SUM(B4:B33),B33)<o></o>
<o> </o>
problem
The formula that I have, works for the day in question (resetting first day back after five days off, to only the hours worked that day and not an accumulative). <o></o>
However it is the subsequent days that the problem occurs, because the formula still totals the previous 30 days, not taking into account that the count has been reset. <o></o>
<o> </o>
Question
I understand that I need to add something extra, but I just can’t figure it out, any help would be great, and very much appreciated.<o></o>
<o> </o>

 

[Andrew Fergus]

Hi and welcome to MrExcel!

I think you need a helper column so in cell R32 use the following formula:

=IF(Q31=0,1,MIN(30,R31+1)) and copy it up and down - this provides a counter for how many days back you need to look (up to a maximum of 30). A similar formula can be used for the other values (i.e. 42 & 90).

Then use the following formula in row 33 to add the rows you want:
=SUM(OFFSET(B33,1-R33,0,R33)) and copy it up and down. The offset function starts at a cell and selects a range based on how far you want to offset the cell (in this case B33 is selected and then changed by moving up by 1 cell less the value in R33) and the range you want to select (in this case the value in R33). So the formula in row 33 would only sum B33 while the formula in row 44 would sum B33:B34.

I'm not 100% sure but I think this will do what you want.

HTH, Andrew

 

[numbers numbers]

thats great you got it, took me ages and i still didnt have it pinned. very cool thank you.
one small change
=IF(Q31=0,1,MIN(30,R31+1))
should read
=IF(Q31=0,MIN(30,R31+1),1)