Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
I'm trying to average the values in approximately 250 non-adjacent cells
(there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
=SUM([cells])/COUNT([cells]
But even there you may run into limits. Alternatively, why don't you sum up a few groups of the cells, say 5 groups of 50, sum those 5 sums together and divide by 250? Dave -- Brevity is the soul of wit. "Tom Young" wrote: I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
You could enter the cell references in a list on another sheet. Then
enter an array formula like (ctrl+shift+enter to execute): =AVERAGE(N(INDIRECT(Sheet2!A1:A250))) An alternative way to get the list of references would be to define the name Selection to refer to =SELECTION() and then enter down the column: =CELL("address",INDEX(Selection,,,ROW())) Now when you select a range of cells and press Ctl+Alt+F9, the range will update with the current selection, ignore any circular reference messages. When you have selected all 250 references, copy and paste values to fix the list. Tom Young wrote: I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Tom, I did the following experiment:
In range A1:G22 I selected randomly 4 cells. I entered their addresses in H1:H4 (e.g. A3, G4 etc). Then I used the following formula: =SUMPRODUCT(A1:G22*ISNUMBER(MATCH(ADDRESS(ROW(A1:G 22),COLUMN(A1:G22),4),H1:H4,0)))/SUMPRODUCT(--ISNUMBER(MATCH(ADDRESS(ROW(A1:G22),COLUMN(A1:G22), 4),H1:H4,0))) It worked. Does this provide a basis for what you want to do? One thing that can complicate the formula is having the cells in different worksheets. ALso if the containing range is too big SUMPRODUCT might have a problem. In this case you can use SUM instead of SUMPRODUCT but you must array enter it (Ctrl+Shift+Enter). Another thing that can simplify your task is to first color the cells that you want to include and then use a user-defined formula to build the list of cells automatically. There are several posts in this group that show how to write such a function that can detect the format. HTH Kostis Vezerides Tom Young wrote: I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Roger, this only works for small ranges. Named ranges have a limit of
255 characters and references are stored in the format =Sheet1!A1,Sheet1!A2,... which limits the selection to about 20 such cells. Roger Govier wrote: Hi Tom Create a named range for your cells to be averaged. Select your range of cells by holding down Ctrl as you click on each one Enter Myrange into the Name box, (just above row Number 1 and to the left of column A) and press Enter. =AVERAGE(Myrange) -- Regards Roger Govier "Tom Young" <Tom wrote in message ... I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Note: there is 255 character limit in the "refers to:" dialog so may not work.
Another note: hit SHIFT + F8 to enable "ADD" mode and you do not have to hold CTRL key while selecting non-adjacent cells. Gord Dibben MS Excel MVP On Mon, 30 Oct 2006 15:53:34 -0000, "Roger Govier" wrote: Hi Tom Create a named range for your cells to be averaged. Select your range of cells by holding down Ctrl as you click on each one Enter Myrange into the Name box, (just above row Number 1 and to the left of column A) and press Enter. =AVERAGE(Myrange) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Lori, you are quite right.
Tom could create a series of such ranges (within the limits for each) and use rng1, rng2 etc as the names then =AVERAGE(rng1,rng2, ..... rng13) A bit messy, but it would work. -- Regards Roger Govier "Lori" wrote in message oups.com... Roger, this only works for small ranges. Named ranges have a limit of 255 characters and references are stored in the format =Sheet1!A1,Sheet1!A2,... which limits the selection to about 20 such cells. Roger Govier wrote: Hi Tom Create a named range for your cells to be averaged. Select your range of cells by holding down Ctrl as you click on each one Enter Myrange into the Name box, (just above row Number 1 and to the left of column A) and press Enter. =AVERAGE(Myrange) -- Regards Roger Govier "Tom Young" <Tom wrote in message ... I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Hi Gord
Note: there is 255 character limit Noted!!! See my response to Lori SHIFT + F8 to enable "ADD" mode had not realised that, Gord, it makes it much simpler. Many thanks. -- Regards Roger Govier "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Note: there is 255 character limit in the "refers to:" dialog so may not work. Another note: hit SHIFT + F8 to enable "ADD" mode and you do not have to hold CTRL key while selecting non-adjacent cells. Gord Dibben MS Excel MVP On Mon, 30 Oct 2006 15:53:34 -0000, "Roger Govier" wrote: Hi Tom Create a named range for your cells to be averaged. Select your range of cells by holding down Ctrl as you click on each one Enter Myrange into the Name box, (just above row Number 1 and to the left of column A) and press Enter. =AVERAGE(Myrange) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Lori
Info only............... Note: there is a limit of about 25 - 30 cells to a range using this method, due to a 255 character limit in a named range, and to the fact that the cells in 'Refers to' field get the sheet name pre-pended to them. Longer sheet names will reduce the this number even more. This can be circumvented, if more are needed, they can be manually entered in the 'Refers to' box. Whilst the limit is 255 characters in the 'Name' definition, as an example, it is possible to define a range of 46 non-contiguous cells, with the following string: =$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4 ,$B$6,$D$6,$F$6,$H$6,$J$6,$B$8,$D$8,$F$8, $H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$1 2,$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14, $J$14,$B$16,$D$16,$F$16,$H$16,$J$16,$B$18,$D$18,$F $18,$H$18,$J$18,$L$3 As an extra hint. In the example above all of the cell references are absolute. Typing all of that out can be time-consuming and difficult. It would be easier to enter the cell references as relative references (=B2,D2,F2, etc.), and then convert them to absolute simply by hitting F2 to enter edit mode, select the complete string, and then F4 to convert to absolute. Further F4s will convert to relative/absolute, absoulte/relative, and then back to relative. Gord Dibben MS Excel MVP On 30 Oct 2006 08:16:01 -0800, "Lori" wrote: Roger, this only works for small ranges. Named ranges have a limit of 255 characters and references are stored in the format =Sheet1!A1,Sheet1!A2,... which limits the selection to about 20 such cells. Roger Govier wrote: Hi Tom Create a named range for your cells to be averaged. Select your range of cells by holding down Ctrl as you click on each one Enter Myrange into the Name box, (just above row Number 1 and to the left of column A) and press Enter. =AVERAGE(Myrange) -- Regards Roger Govier "Tom Young" <Tom wrote in message ... I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Gord - This all works but entering text by hand into the name box is
not really practical and also error prone. Listing the references on a sheet and using the indirect function approach is not a bad solution as its quite clear and only needs references in A1 format. The list of references can be obtained from the current selection either with the method above (which worked well in tests) or a more general VBA routine. Roger's multiple range approach is also workable and simple but maybe a little less transparent in that the ranges are all hidden in separate names. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Thanks for the insight guys and multiple suggestions. Now i've got some
thinking to do in order to determine the best way to proceed. On another front, I need to average several cells (not enough to trigger the limits discussed above) that currently have nothing in them. I want to go ahead and set the formula up though. By doing this, I get the #DIV/0! error. Is there any way to have the result cell just be blank rather than #DIV/0!? Of course, once some values are entered into the cells it will be a mute point. But, that could be some time from now and I need to have the formulas in place. "Tom Young" wrote: I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Good points Lori.
Thanks, Gord On 30 Oct 2006 09:53:31 -0800, "Lori" wrote: Gord - This all works but entering text by hand into the name box is not really practical and also error prone. Listing the references on a sheet and using the indirect function approach is not a bad solution as its quite clear and only needs references in A1 format. The list of references can be obtained from the current selection either with the method above (which worked well in tests) or a more general VBA routine. Roger's multiple range approach is also workable and simple but maybe a little less transparent in that the ranges are all hidden in separate names. Gord Dibben MS Excel MVP |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Hi Tom
Something like =IF(COUNT(G1:K1)=0,"",AVERAGE(G1:K1)) substituting whatever you have for your ranges in place of G1:K1 -- Regards Roger Govier "Tom Young" wrote in message ... Thanks for the insight guys and multiple suggestions. Now i've got some thinking to do in order to determine the best way to proceed. On another front, I need to average several cells (not enough to trigger the limits discussed above) that currently have nothing in them. I want to go ahead and set the formula up though. By doing this, I get the #DIV/0! error. Is there any way to have the result cell just be blank rather than #DIV/0!? Of course, once some values are entered into the cells it will be a mute point. But, that could be some time from now and I need to have the formulas in place. "Tom Young" wrote: I'm trying to average the values in approximately 250 non-adjacent cells (there is no pattern to the cell location). My first obstacle was the 30 argument limit, which I hoped to get around by using =AVERAGE (()). In another thread someone referred to this as using "multiple area ranges." That seemed to be working fine until about one hour into the tedious process of selecting cells while holding down ctrl, I get the "too many characters" notification. Surely there is some efficient way of averaging more than 30 non-adjacent cells?? I can't believe that Excel makes it this difficult to do a simple calculation. Any suggestions? Thanks. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
In case you have a mixture of contiguous and non-contiguous cells
and you don't want to create a list of 250 addresses, here is a variation on Lori's theme: Select the 250 cells with Gord's method and conditionally format them with any criteria. No other cells on that sheet should have CF. Insert Name Define My250 Refers To: =Selection() Enter this formula into some cell: =Average(My250) Go To Special Conditional Formats Ctrl+Alt+F9 Copy Paste Special Value Lori: What are some other undocumented functions like Select and Evaluate that apply to Refers To? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long
Tom - To discount blanks in the above formula you could use:
IF(ISBLANK(...),"",N(...)) in place of N(...). Herbert - Yes, this would work well for non-contiguous regions and discount blanks. Commands for saving the current selection are View Custom Views Add and File Save Workspace which would allow the selection to be easily recovered. For other functions refer to the information returning functions in the XL4 macro help file: http://www.microsoft.com/downloads/d...displaylang=en Another useful macro function in this context is REFTEXT but also has a 255 limit for others see the help file at: Herbert Seidenberg wrote: In case you have a mixture of contiguous and non-contiguous cells and you don't want to create a list of 250 addresses, here is a variation on Lori's theme: Select the 250 cells with Gord's method and conditionally format them with any criteria. No other cells on that sheet should have CF. Insert Name Define My250 Refers To: =Selection() Enter this formula into some cell: =Average(My250) Go To Special Conditional Formats Ctrl+Alt+F9 Copy Paste Special Value Lori: What are some other undocumented functions like Select and Evaluate that apply to Refers To? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Need help setting the worksheet header/Footer margins based on string height? | Excel Discussion (Misc queries) | |||
Can't add 7th IF statement to long formula. | Excel Worksheet Functions | |||
More than 3 conditional formats? | Excel Discussion (Misc queries) | |||
Date & Time | New Users to Excel |