YIKES! My CSE Formula went south on me

RAN_MPA

New Member
Joined
Jun 12, 2002
Messages
43
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))}

instead of:

{=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?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,972
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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:

http://makeashorterlink.com/?S4B751171

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).
 

RAN_MPA

New Member
Joined
Jun 12, 2002
Messages
43

ADVERTISEMENT

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.
 

RAN_MPA

New Member
Joined
Jun 12, 2002
Messages
43
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
 

RAN_MPA

New Member
Joined
Jun 12, 2002
Messages
43
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.
 

RAN_MPA

New Member
Joined
Jun 12, 2002
Messages
43
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.
 

Forum statistics

Threads
1,144,449
Messages
5,724,414
Members
422,552
Latest member
Ajit Kumar_1982

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top