Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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" ? |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function with 3 arguments | Excel Worksheet Functions | |||
Function Arguments | Excel Worksheet Functions | |||
Is it possible to use more than 8 arguments in a function? | Excel Discussion (Misc queries) | |||
AVERAGE more than 30 arguments? | Excel Worksheet Functions | |||
How do I place an average bar gray-scale in the background? | Charts and Charting in Excel |