Average Calculations

K

Kgov

I need a formula that can look at a cell determine if the value is 1,2,3 or 4
(years) and based on that calculate the average from another sheet...so the
user would select 3 years and the formula would then go to tab2 look at the
last 5 years worth of data and calculate the average based on 3 years.....Help

tab1
A B
yrs
1 3 \$23 (?formula?) Could be 1-5

Tab 2
A B C D E F
1 Years 2005 2006 2007 2008 2009
2 Oper Exp \$25 \$10 \$14 \$22 \$33 3 yr avg \$23

2

L

Luke M

In B2:
=AVERAGE(OFFSET('Sheet2'!\$F\$2,0,0,1,-A2))

B

Bernie Deitrick

=SUMIF('Tab 2'!B1:F1,">=" & 2010-A1,'Tab 2'!B2:F2)/A1

HTH,
Bernie
MS Excel MVP

K

Kgov

I used the following formula but it didn't return any value??
SUMIF(Qtrly_Actuals!AB11:AE11,">=" & 2010-'FY
Forecast'!J11,Qtrly_Actuals!AB11:AE11)/'FY Forecast'!J11

Any thoughts>>
Thanks so much

B

Bernie Deitrick

Assuming that your year values are in row 11, and numbers to average in 12, then you want to offset
the values that you are summing by one row...
= SUMIF(Qtrly_Actuals!AB11:AE11,">=" & 2010-'FY Forecast'!J11,Qtrly_Actuals!AB12:AE12)/'FY
Forecast'!J11
Though you may need this if years are in 10:
= SUMIF(Qtrly_Actuals!AB10:AE10,">=" & 2010-'FY Forecast'!J11,Qtrly_Actuals!AB11:AE11)/'FY
Forecast'!J11

HTH,
Bernie
MS Excel MVP

K

Kgov

Any other thoughts? The rows are offset. I've tried to play around but it
still returns a zero value.

B

Bernd P

Hello,

Enter into B1:
=IF(AND(A1>0,A1<LOOKUP(2,1/(Sheet2!B2:IV2<>""),COLUMN(Sheet2!
B2:IV2))),AVERAGE(INDEX(Sheet2!2:2,1,LOOKUP(2,1/(Sheet2!
B2:IV2<>""),COLUMN(Sheet2!B2:IV2))-A1+1):INDEX(Sheet2!2:2,1,LOOKUP(2,1/
(Sheet2!B2:IV2<>""),COLUMN(Sheet2!B2:IV2)))),1/0)

This is a normal formula, not an array-formula. It is taking care of
legal values in cell A1 and of additional values for future years in
Sheet2, row 2.
Please notice that it is assuming that there are no other values in
Sheet2, row2 apart from operating expenses. If there are, you might
want to adapt the formula to look for the first 0 (zero) after
operating expense values...

Regards,
Bernd

B

Bernie Deitrick

What does

=COUNTIF(Qtrly_Actuals!AB11:AE11,">=" & 2010-'FY Forecast'!J11)

(or --- =COUNTIF(Qtrly_Actuals!AB10:AE10,">=" & 2010-'FY Forecast'!J11) )

return?

HTH,
Bernie
MS Excel MVP

K

Kgov

I just can't get it to work. I appreciate all of your help!

B

Bernie Deitrick

Send me a copy of your workbook, and I will take a look.

HTH,
Bernie
MS Excel MVP

K

Kgov

If so, what is that?

B

Bernie Deitrick

Hit reply, then edit the address - take out the spaces, change the dot to . etc. I don't like to
post a working email address to keep down spam..

HTH,
Bernie
MS Excel MVP