 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008How 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? 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 > 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? 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? > Hi, > [quoted text clipped - 13 lines] > > > > What should I do? 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)) > 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 [quoted text clipped - 25 lines] > > > > > > What should I do? 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) > 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? 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. > If the arguments are evenly spaced (eg every 3rd cell...A1, A4, A7, etc), > let us know. [quoted text clipped - 11 lines] > > > > What should I do? 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) > Sorry for not being more specific. The arguments are not evenly spaced. > I'm [quoted text clipped - 21 lines] >> > >> > What should I do? 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" ? > Perhaps there's a particular pattern associated with the cells you want to > average. [quoted text clipped - 37 lines] > >> > > >> > What should I do? 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) > the two main categories are "FP"(for profit) and "NP"(non profit). One > column [quoted text clipped - 49 lines] >> >> > >> >> > What should I do? 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? > I think I may have something you can use.... > [quoted text clipped - 73 lines] > >> >> > > >> >> > What should I do? 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) > This is just about right... but to get the information from four different > columns, I've done something that looks more like this: [quoted text clipped - 91 lines] >> >> >> > >> >> >> > What should I do? beautiful! Thank you very much for your help > Try this... > [quoted text clipped - 107 lines] > >> >> >> > > >> >> >> > What should I do? You're welcome....I'm glad I could help. *********** Regards, Ron XL2003, WinXP > beautiful! > [quoted text clipped - 111 lines] > > >> >> >> > > > >> >> >> > What should I do? Try autofilter for FP or NP then use the SUBTOTAL Function. =SUBTOTAL(1,A1:A1000) The "1" is Average Gord Dibben MS Excel MVP >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" ?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.