Problem using Find and Replace ,B with ,C within an Excel Formula

[DarrellK]

Hi,
I am trying to replace ,B with ,C in the following formula and get an
incorrect result.

=SUMPRODUCT(BB$2:BB$377,BL$2:BL$377)/(SUMPRODUCT(BB$2:BB$377,BO$2:BO$377)+SUMPRODUCT(BB$2:BB$377,BG$2:BG$377)+SUMPRODUCT(BB$2:BB$377,BH$2:BH$377)+SUMPRODUCT(BB$2:BB$377,BI$2:BI$377)+SUMPRODUCT(BB$2:BB$377,BJ$2:BJ$377)+SUMPRODUCT(BB$2:BB$377,BK$2:BK$377)+SUMPRODUCT(BB$2:BB$377,BL$2:BL$377)+SUMPRODUCT(BB$2:BB$377,BM$2:BM$377)+SUMPRODUCT(BB$2:BB$377,BN$2:BN$377))

When I do a find and replace of ,B with ,C Excel replaces the :B with :C
instead of replacing ,B with ,C . See the result below after the find and
replace:

=SUMPRODUCT(BB$2:BB$377,BL$2:CL$377)/(SUMPRODUCT(BB$2:BB$377,BO$2:CO$377)+SUMPRODUCT(BB$2:BB$377,BG$2:CG$377)+SUMPRODUCT(BB$2:BB$377,BH$2:CH$377)+SUMPRODUCT(BB$2:BB$377,BI$2:CI$377)+SUMPRODUCT(BB$2:BB$377,BJ$2:CJ$377)+SUMPRODUCT(BB$2:BB$377,BK$2:CK$377)+SUMPRODUCT(BB$2:BB$377,BL$2:CL$377)+SUMPRODUCT(BB$2:BB$377,BM$2:CM$377)+SUMPRODUCT(BB$2:BB$377,BN$2:CN$377))

Please advise.

Thanks.

Darrell

 

[Bernie Deitrick]

Darrell,

Change the formula to a string first, by pressing F2, Home, and single quote. Then do the
replacements, and then press F2, Home, Del to revert to a formula.

HTH,
Bernie
MS Excel MVP

 

[Billy Liddel]

Darrell

I used Find replace twice, the first does as you said, the second changes
the first part of the range which is what I imagine you need. See below

=SUMPRODUCT(BB$2:BB$377,CL$2:CL$377)/(SUMPRODUCT(BB$2:BB$377,CO$2:CO$377)+SUMPRODUCT(BB$2:BB$377,CG$2:CG$377)+SUMPRODUCT(BB$2:BB$377,CH$2:CH$377)+SUMPRODUCT(BB$2:BB$377,CI$2:CI$377)+SUMPRODUCT(BB$2:BB$377,CJ$2:CJ$377)+SUMPRODUCT(BB$2:BB$377,CK$2:CK$377)+SUMPRODUCT(BB$2:BB$377,CL$2:CL$377)+SUMPRODUCT(BB$2:BB$377,CM$2:CM$377)+SUMPRODUCT(BB$2:BB$377,CN$2:CN$377))

Regards
Peter

 

[DarrellK]

Hi Billy,
Yes, it does appear to provide me with the correct result after I apply the
"find adn replace" twice. But why does this happen? Why does it first replace
the :B with :C and then ,B with ,C? I am now thinking that maybe this happens
because the leftmost column in the array must always be of a lower
alphabetical order than the rightmost column of an array (i.e. it cannot go
CL$2:BL$377) and therefore it must replace the rightmost column index first
and then the leftmost column index.

Thanks for your quick response.

Darrell