Look Up Help Needed Again

[Bob]

Hi,

I had a similar question yesterday, related to a table of data that gets
updated daily. The top row is this month's date, and the row below is the
bank balance for that particular day. Then, the final column (to the right)
is for totals. See below:


Daily Total
Dates| 7/1 7/2 7/3 etc.
Value| $1 $4 $5 etc. ?


Can someone help me create a formula that will add up the most recent seven
days worth of bank balances? For example, after I input today's (7/24)
balance I will want the formula to automatically sum July 18th - July 24th's
balances.

Thanks in advance for your help!

 

[RagDyeR]

Say your 31 days go from B2 to AF2.

Enter this *array* formula in AG2:

=SUM(INDEX(B2:AF2,LARGE(COLUMN(A:AE)*(B2:AF2<>""),7)):AF2)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Copy down as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Hi,

I had a similar question yesterday, related to a table of data that gets
updated daily. The top row is this month's date, and the row below is the
bank balance for that particular day. Then, the final column (to the right)
is for totals. See below:


Daily Total
Dates| 7/1 7/2 7/3 etc.
Value| $1 $4 $5 etc. ?


Can someone help me create a formula that will add up the most recent seven
days worth of bank balances? For example, after I input today's (7/24)
balance I will want the formula to automatically sum July 18th - July 24th's
balances.

Thanks in advance for your help!

 

[RagDyeR]

Forgot to mention that the above formula will total the row even if there's
less then 7 entries.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Say your 31 days go from B2 to AF2.

Enter this *array* formula in AG2:

=SUM(INDEX(B2:AF2,LARGE(COLUMN(A:AE)*(B2:AF2<>""),7)):AF2)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Copy down as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Hi,

I had a similar question yesterday, related to a table of data that gets
updated daily. The top row is this month's date, and the row below is the
bank balance for that particular day. Then, the final column (to the right)
is for totals. See below:


Daily Total
Dates| 7/1 7/2 7/3 etc.
Value| $1 $4 $5 etc. ?


Can someone help me create a formula that will add up the most recent seven
days worth of bank balances? For example, after I input today's (7/24)
balance I will want the formula to automatically sum July 18th - July 24th's
balances.

Thanks in advance for your help!

 

[Japazo88]

Assuming your data is all in row 1 and row 2...you can use a series of MAX()
and HLookup() functions to do what you want.

in your desired free-space, lets say g5, enter '=max(1:1)'. This will give
you your most current date. Now, one cell left of that (f5), enter the same
formula but subtract one. ie: '=max(1:1)-1'. That will give you your most
current date minus one day. Continue that process for as many days as you
prefer.

Now, below the cell containing your most current day formula, g5, use an
hlookup() to find the corresponding monetary value. You will type,
'hlookup(G5,1:1,2:2,2,false)'. This will take the value in G5, look for it in
rows 1:1 and 2:2, then return the value in the row below the found value
(your money cell). Then once you have done that for each box, you can simply
sum() all the subtotal boxes to get your 7 day total.

If this doesnt make sense Ill explain it better, hope it helps!