 |
 | 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 / September 2007Averaging non-continuous ranges
Hi, I want to average a number of values that don't fall in one block, e.g. A1 A4 A7 etc. The problem being that there are more than 30 of them, and the Average function is limited to 30 individual cells (or ranges) for my version of Excel. Is there any way I can work around this? The cells will have a constant offset from each other, if that helps.  SignatureRegards, Paul Hyett, Cheltenham There are two ways I can think of 1) Create a named range of the non-continuous range. In your formula put =AVERAGE(myNamedRange) and replace with your range 2) Create a User Defined Function to calculate the average of the entered range. This one is much more complicated than (1) above. SignatureHTH, Barb Reinhardt > Hi, > [quoted text clipped - 12 lines] > > The cells will have a constant offset from each other, if that helps. Hi Paul One way Highlight the cells by holding down control as you click each one, and give it a name e.g. MyRng by typing that into the Name box (left of column A and above Row 1) =Average(MyRng) If there are too many cells to fit to one named range, create several and use =Average(MyRng1+MyRng2 etc...) SignatureRegards Roger Govier > Hi, > [quoted text clipped - 12 lines] > > The cells will have a constant offset from each other, if that helps. Try this which I sized for 40 cells: =SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*(A1:A118<>"")) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Hi, I want to average a number of values that don't fall in one block, e.g. A1 A4 A7 etc. The problem being that there are more than 30 of them, and the Average function is limited to 30 individual cells (or ranges) for my version of Excel. Is there any way I can work around this? The cells will have a constant offset from each other, if that helps. SignatureRegards, Paul Hyett, Cheltenham BTW, If, in the future, your cells do *not* have a constant offset, you can still reference *more* then 30 cells by simply enclosing the references in *DOUBLE* parens. =Average((1,2,3, ... 100,101, ...etc.)) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Try this which I sized for 40 cells: =SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*(A1:A118<>"")) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Hi, I want to average a number of values that don't fall in one block, e.g. A1 A4 A7 etc. The problem being that there are more than 30 of them, and the Average function is limited to 30 individual cells (or ranges) for my version of Excel. Is there any way I can work around this? The cells will have a constant offset from each other, if that helps. SignatureRegards, Paul Hyett, Cheltenham >Try this which I sized for 40 cells: > >=SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A1 >=18)+2,3)=0)*(A1:A118<>"")) This looks interesting, although I'll have to read up on SUMPRODUCT & MOD, as I like to understand formulas I'm using if I can. Thanks for your help, and everyone else's too. SignaturePaul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Have you tried =AVERAGE(A1,A4,A7) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi, > [quoted text clipped - 12 lines] > > The cells will have a constant offset from each other, if that helps. >Have you tried > >=AVERAGE(A1,A4,A7) Yes - but you're only allowed 30 cells, and - sods law - I have 31... :) SignaturePaul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) try this idea =AVERAGE(AVERAGE(A1,A4,A7),AVERAGE(A10,A12)) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > >>Have you tried >> >>=AVERAGE(A1,A4,A7) >> > Yes - but you're only allowed 30 cells, and - sods law - I have 31... :) Hello, That's only mathematical correct if ALL of your "sub"AVERAGEs have the same number of arguments. For primes like 31 it won't be possible... Regards, Bernd A bit more explanation would be helpful. I did test with the formula presented. However the (( )) is better. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hello, > [quoted text clipped - 5 lines] > Regards, > Bernd That's another post in this thread that I can't see, I don't see Bernd P's post at all. Damned Outlook Express! SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >A bit more explanation would be helpful. I did test with the formula >presented. However the (( )) is better. [quoted text clipped - 8 lines] >> Regards, >> Bernd I'm not sure that that is what it is. I'm using vista winmail SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > That's another post in this thread that I can't see, I don't see Bernd P's > post at all. [quoted text clipped - 13 lines] >>> Regards, >>> Bernd In microsoft.public.excel on Thu, 13 Sep 2007, Sandy Mann <sandymann2@mailinator.com> wrote : >That's another post in this thread that I can't see, I don't see Bernd P's >post at all. I cancelled my original post as I accidentally used a wrong email addy. SignaturePaul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) Try enclosing it in double parenthesis: >>=AVERAGE((A1,A4........,Z29,AA30,AB31)) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >>Have you tried >> >>=AVERAGE(A1,A4,A7) >> > Yes - but you're only allowed 30 cells, and - sods law - I have 31... :) Nice SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Try enclosing it in double parenthesis: > [quoted text clipped - 5 lines] >>> >> Yes - but you're only allowed 30 cells, and - sods law - I have 31... :) > Nice Ragdyer's nice and he said he saw it when an OP posted back and said he found the answer himself. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Nice >> Try enclosing it in double parenthesis: [quoted text clipped - 6 lines] >>>> >>> Yes - but you're only allowed 30 cells, and - sods law - I have 31... :) I wonder why that post did not appear for ME?? SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> >> Nice [quoted text clipped - 13 lines] >>>> Yes - but you're only allowed 30 cells, and - sods law - I have 31... >>>> :) http://groups.google.co.uk/groups?as_q=&num=10&scoring=r&hl=en&as_epq=&as_oq=&as _eq=&as_ugroup=&as_usubject=Sum+error+when+adding+31+days&as_uauthors=Ragdyer&lr =&as_qdr=&as_drrb=b&as_mind=11&as_minm=9&as_miny=1981&as_maxd=12&as_maxm=9&as_ma xy=2007&safe=off Although I had difficulty in finding it in Google. I hope that they get it sorted out soon. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > I wonder why that post did not appear for ME?? >>> [quoted text clipped - 14 lines] >>>>> Yes - but you're only allowed 30 cells, and - sods law - I have 31... >>>>> :) Yea, but that's the one from *yesterday*! Can't you guys see my 2nd post in *this* thread ... same thing, only pertaining to Average(()), almost 16 hours ago? Is something wrong with the MS servers again? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- http://groups.google.co.uk/groups?as_q=&num=10&scoring=r&hl=en&as_epq=&as_oq=&as _eq=&as_ugroup=&as_usubject=Sum+error+when+adding+31+days&as_uauthors=Ragdyer&lr =&as_qdr=&as_drrb=b&as_mind=11&as_minm=9&as_miny=1981&as_maxd=12&as_maxm=9&as_ma xy=2007&safe=off > Although I had difficulty in finding it in Google. I hope that they get it > sorted out soon. [quoted text clipped - 17 lines] > >>>>> Yes - but you're only allowed 30 cells, and - sods law - I have 31... > >>>>> :) I'm using msnews and never saw that post. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Yea, but that's the one from *yesterday*! > [quoted text clipped - 31 lines] >> >>>>> me) You said "saw", Does that mean that you can "see" it now? Google doesn't seem to miss it though. http://tinyurl.com/2rmrc4 I myself have read threads, where some posts appear out of context, or the OP is missing. When it's obvious that something is not there, I try Google to look up the entire thread. However, when it's *not* obvious, one would not even be aware that anything is *not there*. At times, when threads contain a conversation like this, here ... I Google the thread, just out of curiosity. I find that when I'm missing posts, it seems to come in "spurts", a week or so of numerous occurrences, then everything looking fine for months. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- I'm using msnews and never saw that post. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Yea, but that's the one from *yesterday*! > [quoted text clipped - 33 lines] >> >>>>> me) RD, Again, using msnews.public.excel I have only ever seen these 2 posts from you pertaining to this thread and the one yesterday asking the question. ===== Try this which I sized for 40 cells: =SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*A1:A118)/SUMPRODUCT((MOD(ROW(A1:A118)+2,3)=0)*(A1:A118<>"")) HTH, RD ===== BTW, If, in the future, your cells do *not* have a constant offset, you can still reference *more* then 30 cells by simply enclosing the references in *DOUBLE* parens. =Average((1,2,3, ... 100,101, ...etc.)) SignatureHTH, RD ==== -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > You said "saw", > Does that mean that you can "see" it now? [quoted text clipped - 56 lines] >> 31... >>> >>>>> :) Hi RD, > Is something wrong with the MS servers again? Perhaps it is just me but I have been having trouble finding posts on Google that I know are there. Because I have been missing posts in OE, ( and sometimes replying saying that no one else had replied when in fact they had), I have started checking Google to see if someone else has replied. For instance I replied to a post on microsoft.public.worksheet.functions with the subject Formula Help on September 13 2007. The OP posted back 10 hours ago but when I go to Google Groups Advanced Search I cannot find it at all. The parameters that I used were: Return only messages from the group at this location: *EXCEL* Return only messages where the subject contains: Formula Help Dates: 10 Sep 2007 to 14 Sep 2007 Result 18 posts with *Formula* and *Help* in the subject but no *Formula Help* Insert the author as my name and I get no posts returned at all. Yet with: Group: *EXCEL* Subject: Averaging non-continuous ranges Author: Ragdyer I find this thread no bother. Substitute my name for yours and I get no posts returned! The sun shines for other people............ If no one else is having trouble with Google that it must be me but I am not doing anything different to what I use to do and I did not have this degree of difficulty before :-( SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Yea, but that's the one from *yesterday*! > [quoted text clipped - 31 lines] >> >>>>> me) It's been like this for a while now, even if you specify excel it will bring up other newsgroups as well. Somebody mentioned something about using group:microsoft.public.excel* I haven't tried it so I don't know SignatureRegards, Peo Sjoblom > Hi RD, > [quoted text clipped - 72 lines] >>> >>>>> me) Yes thank you Peo, I used to always use microsoft.public.excel* until I someone posted a link here that came up with *EXCEL* already entered. I went back and tried the microsoft.public.excel* and I got 21 posts, (in place of 18 posts before), including two with the subject Formula Help, unfortunately still he wrong ones :-( We can only hope that Google get it sorted out soon, but then that's progress for you! <g> SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > It's been like this for a while now, even if you specify excel it will > bring up other newsgroups as well. Somebody mentioned something about [quoted text clipped - 3 lines] > > I haven't tried it so I don't know In microsoft.public.excel on Wed, 12 Sep 2007, Sandy Mann <sandymann2@mailinator.com> wrote : >Try enclosing it in double parenthesis: > >>>=AVERAGE((A1,A4........,Z29,AA30,AB31)) It worked - I'm not sure *why* it worked, but who cares! :) Thank you. SignaturePaul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) > Wed, 12 Sep 2007, Sandy Mann <sandyma...@mailinator.com> wrote : > >Try enclosing it in double parenthesis: > >>>=AVERAGE((A1,A4........,Z29,AA30,AB31)) > > It worked - I'm not sure *why* it worked, but who cares! :) Look at the union reference operator on the "About calculation operators" Help page. |
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.