Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average calculations w/validation cell
I am using a validation cell to allow the user to pick if they want the
average to be calculated based on 2,3 or 4 years. I'm trying to develop a formula so that after the # of years is picked it will then add 2, 3 or 4 other cells and give the appropriate averge... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average calculations w/validation cell
Tell us about the layout of the data that need to be averaged
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kgov" wrote in message ... I am using a validation cell to allow the user to pick if they want the average to be calculated based on 2,3 or 4 years. I'm trying to develop a formula so that after the # of years is picked it will then add 2, 3 or 4 other cells and give the appropriate averge... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average calculations w/validation cell
Thanks for the advice Bernard.
A bit more information.... A B C 1 2005 20 15 2 2006 12 18 3 2007 22 45 4 2008 16 19 6 2 3 Validation cell. # of years adds last 2, 3 yrs etc. 7 19* 27* always starting with 2008 CY *16+22=38/2=19 *19+45+18=82/3=27 "Kgov" wrote: I am using a validation cell to allow the user to pick if they want the average to be calculated based on 2,3 or 4 years. I'm trying to develop a formula so that after the # of years is picked it will then add 2, 3 or 4 other cells and give the appropriate averge... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average calculations w/validation cell
In cell B7 under the 2 use one of these and copy to B7
a) =SUMPRODUCT(--($A$1:$A$4$A$4-B6),B1:B4)/SUMPRODUCT(--($A$1:$A$4$A$4-B6)) b) =SUMIF($A$1:$A$4,""&$A$4-B6,B1:B4)/COUNTIF($A$1:$A$4,""&$A$4-B6) c) in XL2070 only =AVERAGEIF($A$1:$A$4,""&$A$4-B6,B1:B4) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kgov" wrote in message ... Thanks for the advice Bernard. A bit more information.... A B C 1 2005 20 15 2 2006 12 18 3 2007 22 45 4 2008 16 19 6 2 3 Validation cell. # of years adds last 2, 3 yrs etc. 7 19* 27* always starting with 2008 CY *16+22=38/2=19 *19+45+18=82/3=27 "Kgov" wrote: I am using a validation cell to allow the user to pick if they want the average to be calculated based on 2,3 or 4 years. I'm trying to develop a formula so that after the # of years is picked it will then add 2, 3 or 4 other cells and give the appropriate averge... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average calculations w/validation cell
Thank you- It worked!
Can you tell me what the -- are for? "Bernard Liengme" wrote: In cell B7 under the 2 use one of these and copy to B7 a) =SUMPRODUCT(--($A$1:$A$4$A$4-B6),B1:B4)/SUMPRODUCT(--($A$1:$A$4$A$4-B6)) b) =SUMIF($A$1:$A$4,""&$A$4-B6,B1:B4)/COUNTIF($A$1:$A$4,""&$A$4-B6) c) in XL2070 only =AVERAGEIF($A$1:$A$4,""&$A$4-B6,B1:B4) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kgov" wrote in message ... Thanks for the advice Bernard. A bit more information.... A B C 1 2005 20 15 2 2006 12 18 3 2007 22 45 4 2008 16 19 6 2 3 Validation cell. # of years adds last 2, 3 yrs etc. 7 19* 27* always starting with 2008 CY *16+22=38/2=19 *19+45+18=82/3=27 "Kgov" wrote: I am using a validation cell to allow the user to pick if they want the average to be calculated based on 2,3 or 4 years. I'm trying to develop a formula so that after the # of years is picked it will then add 2, 3 or 4 other cells and give the appropriate averge... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average calculations w/validation cell
A phrase like (A1:A44) returns an array of Boolean values such as {FALSE,
TRUE, TRUE, TRUE} When you perform an arithmetic operation on a Boolean, Excel treated TRUE as 1 and FALSE as zero A convenient thing to do is to double negate the values (change the sign and then change it again) - which means they stay the same but become numbers So --(A1:A44) would then give us {0,1,1,1} J.E McGimpsey is tell all at: http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kgov" wrote in message ... Thank you- It worked! Can you tell me what the -- are for? "Bernard Liengme" wrote: In cell B7 under the 2 use one of these and copy to B7 a) =SUMPRODUCT(--($A$1:$A$4$A$4-B6),B1:B4)/SUMPRODUCT(--($A$1:$A$4$A$4-B6)) b) =SUMIF($A$1:$A$4,""&$A$4-B6,B1:B4)/COUNTIF($A$1:$A$4,""&$A$4-B6) c) in XL2070 only =AVERAGEIF($A$1:$A$4,""&$A$4-B6,B1:B4) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kgov" wrote in message ... Thanks for the advice Bernard. A bit more information.... A B C 1 2005 20 15 2 2006 12 18 3 2007 22 45 4 2008 16 19 6 2 3 Validation cell. # of years adds last 2, 3 yrs etc. 7 19* 27* always starting with 2008 CY *16+22=38/2=19 *19+45+18=82/3=27 "Kgov" wrote: I am using a validation cell to allow the user to pick if they want the average to be calculated based on 2,3 or 4 years. I'm trying to develop a formula so that after the # of years is picked it will then add 2, 3 or 4 other cells and give the appropriate averge... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | 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 | |||
Cell Calculations | Excel Worksheet Functions |