# YIKES! My CSE Formula went south on me

#### RAN_MPA

##### New Member
Many thanks to all that helped me get to the place I'm at now with CSE (Array) formulas. Back in June, Mark W., Yogi and Aladin all provided excellent examples of how to improve my CSE formula to make it work. The CSE formula I finally decided on was Mark W.'s {=sum(if(mod(column(f9:dy9),2),g9:dy9))} .

Now, I have been asked to update my annual reallocation of costs by ADDING A NEW COLUMN of secondary revenue to the worksheet.

As soon as I enter a new column (in this case, column "D"), the CSE formula ceases to evaluate correctly. The new range should be (9:dz9), but the formula doesn't update automatically.

When I re-entered the CSE formula and identify the new range, the various rows do not always equate correctly. One of two (out of twenty) are correct, but the others are not.

I have spent the better part of today re-reading the various CSE posts and trying to update the formula, but to no avail.

Is there a trick for this type of operation (adding a column to the existing worksheet) that I am not catching on to that would make the CSE formula work all the time?

Sorry for the long post, but I thought it necessary to describe what I've been tried so far.

Thanks for any help.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
On 2002-08-30 14:50, RAN_MPA wrote:
Many thanks to all that helped me get to the place I'm at now with CSE (Array) formulas. Back in June, Mark W., Yogi and Aladin all provided excellent examples of how to improve my CSE formula to make it work. The CSE formula I finally decided on was Mark W.'s {=sum(if(mod(column(f9:dy9),2),g9:dy9))} .

Now, I have been asked to update my annual reallocation of costs by ADDING A NEW COLUMN of secondary revenue to the worksheet.

As soon as I enter a new column (in this case, column "D"), the CSE formula ceases to evaluate correctly. The new range should be (9:dz9), but the formula doesn't update automatically.

When I re-entered the CSE formula and identify the new range, the various rows do not always equate correctly. One of two (out of twenty) are correct, but the others are not.

I have spent the better part of today re-reading the various CSE posts and trying to update the formula, but to no avail.

Is there a trick for this type of operation (adding a column to the existing worksheet) that I am not catching on to that would make the CSE formula work all the time?

Sorry for the long post, but I thought it necessary to describe what I've been tried so far.

Thanks for any help.

Shouldn't the formula be either

{=sum(if(mod(column(g9:dy9),2),g9:dy9))}

{=sum(if(mod(column(f9:dy9),2),f9:dy9))}

{=sum(if(mod(column(f9:dy9),2),g9:dy9))}

Also, do you mean that the formula doesn't compute correctly when you insert a new column on the left side of your data?

On 2002-08-30 14:50, RAN_MPA wrote:
Many thanks to all that helped me get to the place I'm at now with CSE (Array) formulas. Back in June, Mark W., Yogi and Aladin all provided excellent examples of how to improve my CSE formula to make it work. The CSE formula I finally decided on was Mark W.'s {=sum(if(mod(column(f9:dy9),2),g9:dy9))} .

Now, I have been asked to update my annual reallocation of costs by ADDING A NEW COLUMN of secondary revenue to the worksheet.

As soon as I enter a new column (in this case, column "D"), the CSE formula ceases to evaluate correctly. The new range should be (9:dz9), but the formula doesn't update automatically.

When I re-entered the CSE formula and identify the new range, the various rows do not always equate correctly. One of two (out of twenty) are correct, but the others are not.

I have spent the better part of today re-reading the various CSE posts and trying to update the formula, but to no avail.

Is there a trick for this type of operation (adding a column to the existing worksheet) that I am not catching on to that would make the CSE formula work all the time?

Sorry for the long post, but I thought it necessary to describe what I've been tried so far.

Thanks for any help.

Your formula is considering alternate columns.

Try restoring the orginal formula [some of the information that you show may be incorrect].

Edit the formula to include just a few columns; Array enter the revision.
Then review the parts of the formula to see what is included. If you understand the formula, you will be able to revise it.

Highlight part of the formula then press F9, and after considering the information press ESC.

Go back to your last working ss. Instead of inserting one column, insert TWO columns.

HTH Dave
This message was edited by Dave patton on 2002-08-31 08:40

I wanted to wait for your reply to my query... but it's getting late...

Try:

=SUMPRODUCT((MOD(COLUMN(\$F\$9:\$DY\$9)-CELL("Col",\$F\$9)+1,2)=0)*(\$F\$9:\$DY\$9))

I published this formula for the first time at:

for a problem involving a columnar range.

The formula won't be affected by inserting and/or deleting columns (on the left side of your data).

Aladin - you're right, I showed the wrong formula on the post (it's correct in the worksheet). And, yes, the formula doesn't equate correctly when I add a column OUTSIDE (to the left) of the data being summed.

Sorry I couldn't get back to your original post quickly; other priorities arose, and the holiday weekend is upon us. I will try your suggested fix Tuesday when we return to work. Thanks, again.

Dave - I tried your first suggestion already, but no luck. I will try the two column suggestion Tuesday.

Thanks.

Dave and Aladin - I've tried both suggested fixes and both work, but only if I add two columns. Adding a single column still produces errors.

I'll work with what I've got so far. Thanks for your help.

On 2002-09-03 10:12, RAN_MPA wrote:
Dave and Aladin - I've tried both suggested fixes and both work, but only if I add two columns. Adding a single column still produces errors.

I'll work with what I've got so far. Thanks for your help.
aaSum RAN_MPA.xls
CDEFGHIJKL
7
8
9100346726135020
10
11
12
Sheet1

The formula should work when you add or delete columns before F.

I don't doubt that your solution works for you, but it continues to only work on my worksheet if two columns are added to the original worksheet.

Your solution I am using (edited for the new range I am using - don't forget I have over twenty rows to sum) is =SUMPRODUCT((MOD(COLUMN(\$H9:\$EA9)-CELL("Col",\$H9)+1,2)=0)*(\$H9:\$EA9))

This formula works for the first line of the worksheet (row 9). When I delete the additional (second) column I inserted earlier to complete my assignment, the other lines (rows 10 to 29) do not return the correct values.

I'm grateful for your help, but I'm not sure what else to do, since I've completed the update of the file for this assignment.

Foy your own edification, if you would like to see the entire worksheet, I can email it to you so you can see what I'm up against. (I'm not sure how else to get it to you if you want to see it.)

Thanks, again.

Aladin - your solution DOES WORK, as you know. Many thanks to being able to see what I was doing wrong on my worksheet(s).

As I stated before; I didn't doubt your solution; just my ability to utilize it correctly.

MANY THANKS.

Replies
1
Views
376
Replies
2
Views
164
Replies
1
Views
259
Replies
3
Views
246
Replies
4
Views
450

1,219,792
Messages
6,150,291
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back