Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Editing Array Formula
I have just recently started to use Array formula that return multiple
results. eg, select a range of cells 1 column x 8 rows and enter =LARGE(DataRange,ROW(INDIRECT("1:8"))) array entered to give a list of the top 8 results My question relates to the difficulty I seem to have editing it to say the top 6 I can easily reselect the range and change the formula. Then get errors for the last two cells. If I try to remove the now redundant cells I get a "You cannot change part of an Array" error In fact the only way I have to make all well seems to be delete and start again. If this the only way? Please help Cheers RES |
#2
|
|||
|
|||
|
#3
|
|||
|
|||
My approach: 1) Select one cell of array formula, copy, and paste in a nearby cell (copy only one cell) 2) Delete old array 3) copy single cell back to original position of first cell. 4) select desired number of cells (active cell on cell with formula in it) 5) Push F2, then ctrl-shft-enter If there's a better way, I'm interested, because I have to do this, too. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=398180 |
#4
|
|||
|
|||
Thanks to both Harlan and MrShorty for responding
My post was mostly about the general subject of editing an array formula that return results into a range of cells. It sounds like the way I am doing it is pretty much the same as MrShorty. Harlan that is a nice trick to mask the fact the return range is covering more cells than are needed for the array. Still not sure I fully have my head round it but will give it a go. many thanks RES |
#5
|
|||
|
|||
(1) Change the array formula to a non-array formula:
Select the range containing the formula. At this point the formula bar should show the formula with curly brackets around it. Click in the formula bar. Don't do any editing, just press CTRL+ENTER (*no* SHIFT). The removes the array formula and simply duplicates the formula in every cell (the formula in the formula bar should no longer have curly brackets around it). If you see all kinds of errors, ignore them. (2) Make the non-array formula into an array formula for the new smaller range: Select the new abbreviated range. Click the formula in the formula bar. Press CTRL+SHIFT+ENTER. (3) Delete (or do whatever else) with the cells no longer part of the array. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have just recently started to use Array formula that return multiple results. eg, select a range of cells 1 column x 8 rows and enter =LARGE(DataRange,ROW(INDIRECT("1:8"))) array entered to give a list of the top 8 results My question relates to the difficulty I seem to have editing it to say the top 6 I can easily reselect the range and change the formula. Then get errors for the last two cells. If I try to remove the now redundant cells I get a "You cannot change part of an Array" error In fact the only way I have to make all well seems to be delete and start again. If this the only way? Please help Cheers RES |
#6
|
|||
|
|||
What am I missing?
Why not simply take the "array" *out of* the array formula? For your particular situation, enter this formula in say B1: =LARGE($A$1:$A$20,ROW(1:1)) *NOW*, simply drag down to copy as many rows as you anticipate that you might need. The formula in each cell is a separate entity, which can be revised and/or deleted as you wish, but *still* maintains it's functionality to return an array of values. What you lose with this type of construction is the ability of the formula to *adjust itself*, or *protect itself*, as far as adding or deleting rows/columns. If you don't anticipate changes to the sheet, or even if you do, but you're aware of this situation, you can more easily utilize formulas in this form. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- wrote in message ... Thanks to both Harlan and MrShorty for responding My post was mostly about the general subject of editing an array formula that return results into a range of cells. It sounds like the way I am doing it is pretty much the same as MrShorty. Harlan that is a nice trick to mask the fact the return range is covering more cells than are needed for the array. Still not sure I fully have my head round it but will give it a go. many thanks RES |
#7
|
|||
|
|||
"Tushar Mehta" wrote in message om... (1) Change the array formula to a non-array formula: Select the range containing the formula. At this point the formula bar should show the formula with curly brackets around it. Click in the formula bar. Don't do any editing, just press CTRL+ENTER (*no* SHIFT). The removes the array formula and simply duplicates the formula in every cell (the formula in the formula bar should no longer have curly brackets around it). If you see all kinds of errors, ignore them. (2) Make the non-array formula into an array formula for the new smaller range: Select the new abbreviated range. Click the formula in the formula bar. Press CTRL+SHIFT+ENTER. (3) Delete (or do whatever else) with the cells no longer part of the array. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have just recently started to use Array formula that return multiple results. eg, select a range of cells 1 column x 8 rows and enter =LARGE(DataRange,ROW(INDIRECT("1:8"))) array entered to give a list of the top 8 results My question relates to the difficulty I seem to have editing it to say the top 6 I can easily reselect the range and change the formula. Then get errors for the last two cells. If I try to remove the now redundant cells I get a "You cannot change part of an Array" error In fact the only way I have to make all well seems to be delete and start again. If this the only way? Please help Cheers RES |
#8
|
|||
|
|||
"Tushar Mehta" wrote...
(1) Change the array formula to a non-array formula: Select the range containing the formula. . . . Actually it'd be easier to press [F2] immediately followed by [Ctrl]+[Shift]+[Enter]. That'll automatically select the entire array range. Then press [F2] immediately followed by [Ctrl]+[Enter]. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can you use count with an array formula similar to using sum | Excel Worksheet Functions | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) | |||
Array formula reference (Corrected) | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |