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



"Bernie Deitrick" wrote:

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" wrote in message
...
Any other thoughts? The rows are offset. I've tried to play around but
it
still returns a zero value.
Thanks for your help

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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


HTH,
Bernie
MS Excel MVP


"Kgov" wrote in message
...
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