Average Calculations

[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

 

[Luke M]

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

 

[Bernie Deitrick]

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


HTH,
Bernie
MS Excel MVP

 

[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

 

[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

 

[Kgov]

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

 

[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

 

[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

 

[Kgov]

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

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[Kgov]

I'm fairly new to this discussion group. Do send it to your email address?
If so, what is that?

 

[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