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...

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...

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...

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...

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...

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...