ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Average Calculations (https://www.excelbanter.com/excel-discussion-misc-queries/242556-average-calculations.html)

Kgov
Average Calculations
 
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
Average Calculations
 
In B2:
=AVERAGE(OFFSET('Sheet2'!$F$2,0,0,1,-A2))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kgov" wrote:

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

Bernie Deitrick
Average Calculations
 
=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



Kgov
Average Calculations
 
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




Bernie Deitrick
Average Calculations
 
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

Kgov
Average Calculations
 
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

Bernd P
Average Calculations
 
Hello,

Enter into B1:
=IF(AND(A10,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
Average Calculations
 
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

Kgov
Average Calculations
 
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

Bernie Deitrick
Average Calculations
 
Send me a copy of your workbook, and I will take a look.

HTH,
Bernie
MS Excel MVP


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

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



"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


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

Bernie Deitrick
Average Calculations
 
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


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



"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


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


All times are GMT +1. The time now is 04:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com