YIKES! My CSE Formula went south on me

[RAN_MPA]

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.

 

[Aladin Akyurek]

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]

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]

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]

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]

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]

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
	C	D	E	F	G	H	I	J	K	L
7
8
9		100		34	67	26	13	50	20
10
11
12
Sheet1

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

 

[RAN_MPA]

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]

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.