Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
I am averaging a column of numbers =Average(A1:A33) If I insert a row
"above" A1 the formula does not adjust to include the inserted row. Is there a way to make this work when you insert above the current row? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
Try,
=AVERAGE(INDIRECT("A1:A33")) Mike "tulip" wrote: I am averaging a column of numbers =Average(A1:A33) If I insert a row "above" A1 the formula does not adjust to include the inserted row. Is there a way to make this work when you insert above the current row? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
On Sep 18, 1:12 pm, Mike H wrote:
Try, =AVERAGE(INDIRECT("A1:A33")) That will leave out the new A34 after the insertion, though. tulip, where is the formula? You can use this much INDIRECT("A1:A" of Mike's formula and then perhaps an OFFSET or ROW function to keep the bottom number dynamic, too. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
That works to include the row inserted above the current row - but when you
insert the row above, it "pushes" the current rows down, so data in A33 is now A34. How do I include the last row of data as it moves "down" because of the insertion at the top? "Mike H" wrote: Try, =AVERAGE(INDIRECT("A1:A33")) Mike "tulip" wrote: I am averaging a column of numbers =Average(A1:A33) If I insert a row "above" A1 the formula does not adjust to include the inserted row. Is there a way to make this work when you insert above the current row? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
Spiky - can you help me with the suggestions you made? I don't know how to
use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A" "Spiky" wrote: On Sep 18, 1:12 pm, Mike H wrote: Try, =AVERAGE(INDIRECT("A1:A33")) That will leave out the new A34 after the insertion, though. tulip, where is the formula? You can use this much INDIRECT("A1:A" of Mike's formula and then perhaps an OFFSET or ROW function to keep the bottom number dynamic, too. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
On Sep 18, 1:44 pm, tulip wrote:
Spiky - can you help me with the suggestions you made? I don't know how to use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A" Well, if the formula is directly below the list it is averaging, like in A34 in your original example: =AVERAGE(INDIRECT("A1:A"&ROW()-1)) It matters where you enter this formula. My example calculates from where it is, less one row. That keeps it dynamic so you can insert either at the top or bottom of the list. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
I will try that. Thanks for your help.
"Spiky" wrote: On Sep 18, 1:44 pm, tulip wrote: Spiky - can you help me with the suggestions you made? I don't know how to use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A" Well, if the formula is directly below the list it is averaging, like in A34 in your original example: =AVERAGE(INDIRECT("A1:A"&ROW()-1)) It matters where you enter this formula. My example calculates from where it is, less one row. That keeps it dynamic so you can insert either at the top or bottom of the list. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
If you can move your Average() formula out of Column A, you might try:
=Average(A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tulip" wrote in message ... I will try that. Thanks for your help. "Spiky" wrote: On Sep 18, 1:44 pm, tulip wrote: Spiky - can you help me with the suggestions you made? I don't know how to use the ROW or OFFSET to capture the new A34. =AVERAGE(INDIRECT("A1:A" Well, if the formula is directly below the list it is averaging, like in A34 in your original example: =AVERAGE(INDIRECT("A1:A"&ROW()-1)) It matters where you enter this formula. My example calculates from where it is, less one row. That keeps it dynamic so you can insert either at the top or bottom of the list. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula adjust for inserted row above current row
Hi,
Simply convert the range to a list by going to Data List. One of the benefits of converting a range to a list is that it auto expands. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "tulip" wrote in message ... I am averaging a column of numbers =Average(A1:A33) If I insert a row "above" A1 the formula does not adjust to include the inserted row. Is there a way to make this work when you insert above the current row? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix a formula, so when a row is inserted it doesn't cha | Excel Discussion (Misc queries) | |||
adjust formula to much info | Excel Discussion (Misc queries) | |||
Can rows be inserted without having to manually adjust formulae | Excel Discussion (Misc queries) | |||
Having the current time inserted w/o updating the current time | Excel Worksheet Functions | |||
how to adjust formula dependent on current month | Excel Discussion (Misc queries) |