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

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?

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

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

"pjr" wrote:

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?

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

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

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

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?

"Mike H" wrote:

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

"pjr" wrote:

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?

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

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" wrote:

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

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

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)


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

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

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

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" wrote:

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?

"Mike H" wrote:

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

"pjr" wrote:

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?

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

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" wrote:

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)


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

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

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

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)


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

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)


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

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

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

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

=SUBTOTAL(1,A1:A1000)

The "1" is Average


Gord Dibben MS Excel MVP

On Wed, 26 Mar 2008 10:22:00 -0700, pjr wrote:

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" ?

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

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" wrote:

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)


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

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)


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

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

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

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

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)


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

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)


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

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

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

beautiful!

Thank you very much for your help

"Ron Coderre" wrote:

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

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)


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

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)


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

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

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

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

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

XL2003, WinXP


"pjr" wrote:

beautiful!

Thank you very much for your help

"Ron Coderre" wrote:

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

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)


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

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)


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

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