changing formula

[S S]

This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no longer
works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

thanks

 

[Guest]

Make your alteration to the formula then with the cursor still in the formula
bar enter the new formula with:-

Ctrl+Shift+Enter

Mike

 

[Pete_UK]

It is an array formula, which means that when you type it in or
subsequently edit it then you must use CTRL-SHIFT-Enter to commit it
rather than the usual Enter. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete

 

[Guest]

It's an array formula. It needs to be committed with Ctrl + Shift + Enter -
i.e. after making the changes, exit edit mode with this key combination.

Greg

 

[Pete_UK]

Actually, a quicker way to make the changes is to highlight the
cell(s) with the formula in and do Find & Replace (CTRL-H):

Find what: J137
Replace with: J183

then click Replace All. This way you do not need to use CSE as you are
not strctly editing the formula - it assumes that the formula works in
the first place.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no
longer works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

Because there is a range of cells being covered, you have an array
formula... you need to commit the formula using Ctrl+Shift+Enter. To do
this, select the cell **then** put the cursor into the formula bar... then
press Ctrl+Shift+Enter and the formula should work again.

Rick

 

[S S]

Thanks to all who answered, problem now sorted.
thanks for all your help.