How can I place more than 30 arguments in an average function?

[pjr]

Is it possible to have more than thirty arguments in a single average
function? The arguments are not contiguous, so i can't just make them into
data points...

What should I do?

 

[Mike H]

Hi,

There are several solutions but due to the lack of data this may not be the
best but it may help:-

=SUM(SUM(Ref1,Ref2,up to 30),SUM(Ref31,Ref32,up to another
30))/SUM(COUNT(Ref1,Ref2,...),COUNT(Ref31,Ref32,...))

You can have up to 30 cell references in each sum and count

Mike

 

[Ron Coderre]

If the arguments are evenly spaced (eg every 3rd cell...A1, A4, A7, etc),
let us know.
There may be options that will increment the cell references automatically.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[pjr]

I have four columns of information, classified in up to eight different
categories. The categories are not listed one after the other, so the Average
formula needs to pull multiple cells from each of the four columns without
them being in a range. I've been using the formula (just an example):
=AVERAGE(a1,a4,a5:a8,b1...) but I have more than 30 inputs.

After reading your response I attempted the formula:
=AVERAGE(AVERAGE(a1,a4,a5:a8,b1),AVERAGE(c1,c4,c5:c8,d1))

But this gave me what I think is an incorrect answer. Did I understand your
response correctly?

 

[pjr]

Sorry for not being more specific. The arguments are not evenly spaced. I'm
making a spreadsheet for the results of an application process. the
applications are listed in order, 1 thru 126, but the formula that I am
making is based on information that shows up throughout the list, independant
of their application number.

 

[Ron Coderre]

Perhaps there's a particular pattern associated with the cells you want to
average.
Example:
.. A word (eg "Total") in a cell to the side of it? or one cell up and to the
left?

Have anything like that?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Mike H]

Hi,

I never mentioned using average, the suggestion I gave was to do it
long-hand i.e sum the elements and divide by the number of elements. You can
do it by extending this example. Each sum and each count can be up to 30
elements and (I think) you can have up to 30 elements

=(SUM(B3,B5,B7,B9,B11,B13,B15)+SUM(C3,C5,C7,C9,C11,C13,C15))/SUM(COUNT(B3,B5,B7,B9,B11,B13,B15),COUNT(C3,C5,C7,C9,C11,C13,C15))

 

[pjr]

the two main categories are "FP"(for profit) and "NP"(non profit). One column
is devoted to distinguishing between the two for each application. Is there a
way to pull data from cells that only sit in the same row as an "FP" or "NP" ?

 

[Ron Coderre]

I think I may have something you can use....

With
A2:A20 contains either "FP" or "NP"
B2:B20 contains amounts

C1: FP
C2: NP

This formula returns the average of the FP values:
D1: =SUMIF($A$2:$A$20,C1,$B$2:$B$20)/COUNTIF($A$2:$A$20,C1)

Copy that formula to D2 to see the average of NP values.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Gord Dibben]

Try autofilter for FP or NP then use the SUBTOTAL Function.

=SUBTOTAL(1,A1:A1000)

The "1" is Average


Gord Dibben MS Excel MVP

 

[pjr]

This is just about right... but to get the information from four different
columns, I've done something that looks more like this:

D1:
=SUMIF($A$2:$A$20,C1,$B$2:$B$20)+SUMIF($A$2:$A$20,C1,$E$2:$E$20)+SUMIF($A$2:$A$20,C1,$f$2:$f$20)/COUNTIF($A$2:$A$20,C1)

The trouble now is that the last +SUMIF part is being divided by the COUNTIF
part before it gets added to the other SUMIF's.

it wont let me bracket or put a parenthesis around all the SUMIF's.

is there another way to change the order of operations?

 

[Ron Coderre]

Try this...

D1:
=(SUMIF($A$2:$A$20,C1,$B$2:$B$20)+SUMIF($A$2:$A$20,C1,$E$2:$E$20)+
SUMIF($A$2:$A$20,C1,$f$2:$f$20))/COUNTIF($A$2:$A$20,C1)

Does that help?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[pjr]

beautiful!

Thank you very much for your help

Try this...

D1:
=(SUMIF($A$2:$A$20,C1,$B$2:$B$20)+SUMIF($A$2:$A$20,C1,$E$2:$E$20)+
SUMIF($A$2:$A$20,C1,$f$2:$f$20))/COUNTIF($A$2:$A$20,C1)

Does that help?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

You're welcome....I'm glad I could help.

***********
Regards,
Ron

XL2003, WinXP