using a cell value as the address in a formula ?

[Guest]

Hello there,

I'm wondering if there is a way I can use those SUM() or Average() functions
with the addresses referring to the value of a certain cell ?

I have my 30-day daily sales data from Column B to AE, and my subtotals (e.g.
=SUM(B2:AE2) / averages are on column AF and AG - I'm just wondering if I
can change the fixed range (B2:AE2) to a cell reference so when I enter 14
into the field, I can have the subtotal of the last 2 weeks instead of the
fixed 30 days ?

I have searched in help on excel and on the web but I wasn't able to find
anything related. Please shed me some lights if you can!!

Many Thanks in Advance!!

Regards,
Steven Vegeta

 

[Harlan Grove]

I have my 30-day daily sales data from Column B to AE, and my subtotals
(e.g. =SUM(B2:AE2) / averages are on column AF and AG - I'm just wondering
if I can change the fixed range (B2:AE2) to a cell reference so when I
enter 14 into the field, I can have the subtotal of the last 2 weeks
instead of the fixed 30 days ?

....

Would B2:AE2 always contain the latest 30 days' data? If so, and if you
entered 14 in cell X99, try

=SUM(INDEX(B2:AE2,31-X99):AE2)

=AVERAGE(INDEX(B2:AE2,31-X99):AE2)

 

[Stan Brown]

Fri, 27 Jul 2007 20:18:00 -0700 from Steven Vegeta

I'm wondering if there is a way I can use those SUM() or Average() functions
with the addresses referring to the value of a certain cell ?

Use the INDIRECT function.

For the specific application you mentioned, OFFSET might be a simpler
choice.