Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing formulas
I'm working with many formulas like the one below, and would like to know if
there is any way of using a "find" and "replace all" function for a formula. Such as if I wanted to change all the Kabul's to Chicago for example. Or will I have to do it manually. =SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1"))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing formulas
ISAF Media Analysis;232429 Wrote: I'm working with many formulas like the one below, and would like to know if there is any way of using a "find" and "replace all" function for a formula. Such as if I wanted to change all the Kabul's to Chicago for example. Or will I have to do it manually. =SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1"))) You can try Ctrl+H replace "Kabul" ( no quotes) with "something_else" Change the options to look in -Formulas- Then click -replace all- -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64890 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing formulas
Use cell reference instead of hardcode in the formula.
E1: holds Kabul H1: holds NEG S1: holds SE 1.1 =SUMPRODUCT((E4:E99=E1)*(H4:H99=H1)*(S4:X99=S1)) "ISAF Media Analysis" wrote: I'm working with many formulas like the one below, and would like to know if there is any way of using a "find" and "replace all" function for a formula. Such as if I wanted to change all the Kabul's to Chicago for example. Or will I have to do it manually. =SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE 1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1"))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Formulas | New Users to Excel | |||
changing formulas | Excel Worksheet Functions | |||
changing formulas | Excel Discussion (Misc queries) | |||
Changing formulas | Excel Discussion (Misc queries) | |||
Formulas Changing | Excel Discussion (Misc queries) |