| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average calculations w/validation cell | Excel Discussion (Misc queries) | |||
| Average Calculations | Excel Discussion (Misc queries) | |||
| Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
| Average of Multiple Calculations | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Linear Mode
