Formula

Could you post an example?

Sorry but they switched me to decaffeinated coffee and I'm having trouble following your description. Could you post an example of what you have now, and what kind of a result you want?

1.10.25
2.10.25
3.9.75
4.8.00
5.10.25
6.10.00
7.5.00
8.6.50
Total of all 8 days =70
The object is to never go over 70 so I have to keep track of what will be available the next day and the next and so on.
I want it to drop the top entry every time I make an entry on the bottom and calculate the total as I go.

This works as long as you dont go back and remove entries

If your work hour values are in column B, then enter the following:
in c2 enter
=SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-7):INDIRECT("b"&COUNTIF(B:B,"<>''")))

in c3 (this will give the 8 day running total)enter:
=SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-6):INDIRECT("b"&COUNTIF(B:B,"<>""")+1))

in c4 (this gives last 7 day total) enter:
=SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-5):INDIRECT("b"&COUNTIF(B:B,"<>""")+2))

in c5 (this gives last 6 day total) enter
=SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-4):INDIRECT("b"&COUNTIF(B:B,"<>""")+3))

Now I think that's what you are asking for- on the other hand, it makes a little more sense to me- so far as I understand your layout, for the equations in c3-c5 to give you instead the DIFFERENCE between that total and 70 hours so you'll know how much time is still available. If you want that instead, modify them as follows:

c3:
=70-(SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-6):INDIRECT("b"&COUNTIF(B:B,"<>""")+1)))

c4:
=70-(SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-5):INDIRECT("b"&COUNTIF(B:B,"<>""")+2)))

c5:
=70-(SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-4):INDIRECT("b"&COUNTIF(B:B,"<>""")+3)))

BTW there's no reason these formulas have to go in the cells I suggested, you could put them anywhere on the sheet.

HTH

C2 is sum of last 8
c3 is sum of last 7
c4 is sum of last 6
c5 is sum of last 5

subtracting 70 from c3 gives you hours to spare in next day
subtracting 70 from c4 gives you hours available over the next 2 days
subtracting 70 from c5 gives you hours available over the next 3 days

Sorry Peggy, the countif part of that last formula is a more than a little unreliable, try this redesign based on Juan Pablo's suggestion:
In C1 enter
=SUMPRODUCT(NOT(ISBLANK(B1:B65535))*1)
In C2 enter
=SUM(INDIRECT("B"&C1-7):INDIRECT("B"&C1))
In C3 enter
=70-SUM(INDIRECT("B"&C1-6):INDIRECT("B"&C1))
In C4 enter
=70-SUM(INDIRECT("B"&C1-5):INDIRECT("B"&C1))
In C5 enter
=70-SUM(INDIRECT("B"&C1-4):INDIRECT("B"&C1))

C1 is total number of cells in column B with data (the data has to be entered contiguously, that is with no intervening blank cells, for this to work)
C2 is sum of the last 8 cells with data
C3 is number of hours left over for following day
C4 as C3 for the following 2 days
C5 for the following 3 days

Thx again to Juan and Rob for helping me with this!

Hi Eric,

Using SUMPRODUCT to determine the last cell used this way, as you note, would be unreliable in case you have blank cells. Moreover, it computes by looking at a too big range (B1:B65535) which is costly if it's done by an array formula or SUMPRODUCT that implicitly behaves as an array formula. As a side note, array-formulas or SUMPRODUCT cannot have whole columns (e.g., B:B) as arguments. By the way, that formula can be shortened to:

=SUMPRODUCT((B1:B65535)>0)+0)

The > op instead of the <> op because of pos numbers to be dealt with. And +0 invokes coercion as does *1.

Following up the structure of the system of formulas that you elaborated, I have an alternative suggestion:

In C1 enter: =MATCH(9.99999999999999E+307,B:B)

In C2 enter: =SUM(INDIRECT(ADDRESS(IF($C$1>8,$C$1-7,COLUMN(B:B)),COLUMN(B:B))&":"&ADDRESS($C$1,COLUMN(B:B))))

In C3 enter: =70-SUM(INDIRECT(ADDRESS(IF($C$1>8,$C$1-6,COLUMN(B:B)),COLUMN(B:B))&":"&ADDRESS($C$1,COLUMN(B:B))))

In C4 enter: =70-SUM(INDIRECT(ADDRESS(IF($C$1>8,$C$1-5,COLUMN(B:B)),COLUMN(B:B))&":"&ADDRESS($C$1,COLUMN(B:B))))

In C5 enter: =70-SUM(INDIRECT(ADDRESS(IF($C$1>8,$C$1-4,COLUMN(B:B)),COLUMN(B:B))&":"&ADDRESS($C$1,COLUMN(B:B))))

Aladin

Thanks Aladin, I'd forgotten about the trick with Match!

And thanks also for taking the time to look at my problem. If I remember from the last time you explained this, Match reports the highest row # with data in it if it fails to find a match , so it avoids the "intervening blank" issue.

If you've the time- I do have a few questions:

1) Since it searches the entire column, how is Match less computationally intense than sumproduct- something to do with the inherent array nature of the latter?

2) Why did countif behave so oddly when I tried to get it to do the same thing as sumproduct or match?

3) Why use the address function instead of just indirect with the B column text-concatenated?

Re: Thanks Aladin, I'd forgotten about the trick with Match!

Yes, I believe so.

I suppose this to be a bug.

The :INDIRECT construct will not work when you move the formula to a different worksheet from the one that has the data it works on. It just creates an address that holds for the worksheet it's in.

Aladin

===========