Add conditional with find and replace

[ervierto]

I created several formulas with slightly different cell references and realized I needed to add an additional conditional at the beginning of the formula. I wanted to do a find a nd replace, but adding the additional conditional at the beginning requires adding a close parentheses at the end. Is there a way to do this or do I have to manually update each cell?

I want to make:
=IF(COUNTIF(...)*100)
into:
=IF(D5=0,"-",IF(COUNTIF(...)*100))

 

[Special-K99]

=IF(COUNTIF(...)*100)

Thats not a proper formula, (I'm ignoring the ... in COUNTIF), there's no return values for TRUE or FALSE and its throwing up an error so you can't have IF(condition*100)
Whats the proper formula?

 

[ervierto]

The stuff in the middle doesn't really matter... I am just trying to do the replace and find and also add the parentheses at the end. When I make the search and replace, I am adding the conditional at the front, which means I have to add an additional parenthesis or it gets an error. So, I was just using some shorthand to take out the superfluous stuff... but...
I want to make to make this:
=IF(COUNTIF('Personal Bests'!$C$2:$C$193,">"&C20)+1<11,COUNTIF('Personal Bests'!$C$2:$C$193,">"&C20)+1,"p"&PERCENTRANK.EXC('Personal Bests'!$C$2:$C$193,C20,2)*100)
Into this:
=IF(C20=0,"-",IF(COUNTIF('Personal Bests'!$C$2:$C$193,">"&C20)+1<11,COUNTIF('Personal Bests'!$C$2:$C$193,">"&C20)+1,"p"&PERCENTRANK.EXC('Personal Bests'!$C$2:$C$193,C20,2)*100))

 

[Peter_SSs]

I assume that this formula is copied down a column.
If so, I also assume that the C20 you have shown inserted in the formula will need to change to C21, C22 etc as the formula moves down.
If both assumptions are correct, then find/replace won't work because there is a different cell reference being inserted in each formula.
However, if my assumptions are correct, then you could just manually change the first formula and copy down.

If either of my assumptions are incorrect, then please give more detail about where the original formulas are and what the need to change to.

 

[ervierto]

At the end, I will need to change the C20 reference, but I don't mind doing that by hand. It is easier to go in and edit one piece in one place rather than editing multiple places. The other formulas all change the specific references, so all the references to $C become $D $E, etc. Changing those manually is a bit more of a pain. I get the sense that my original question does not have an easy answer like Find =IF(Countif%*%)*100) and Replace with =IF(D5=0,"-",IF(COUNTIF(%*%)*100)). (Or some other odd sequence of wildcard characters).

 

[Peter_SSs]

I'm still not fully understanding what you have, where and (exactly) what you are trying to achieve. For the formulas that you are trying to change ..

1. Are they all in a single column or row?
a. If so, why can't you just manually change the first formula and drag down or across?
b. If not where are they exactly?

2. What version of Excel are you using?

 

[ervierto]

I have about 120 formulas primarily spread across three columns and 40 or so rows. The data that they are referencing are spread across rows. So, I believe there is not a way to drag down (or fill or whatever) and have the references update across columns. (In other words if my formula references b2 and I drag to the cell below, it will not say c2 it will say b3.) Additionally, while the formulas have the same structure, each one is referencing a different column. So, the first formula in cell c21 is:
=IF(COUNTIF('Personal Bests'!$C$2:$C$193,">"&C20)+1<11,COUNTIF('Personal Bests'!$C$2:$C$193,">"&C20)+1,"p"&PERCENTRANK.EXC('Personal Bests'!$C$2:$C$193,C20,2)*100)
The formula in the cell "below" that (actually in C23) is:
=IF(COUNTIF('Personal Bests'!$D$2:$D$193,">"&C22)+1<11,COUNTIF('Personal Bests'!$D$2:$D$193,">"&C22)+1,"p"&PERCENTRANK.EXC('Personal Bests'!$D$2:$D$193,C22,2)*100)
And so on across numerous cells. I have already gotten all of those created. I then figured out that I needed to add the additional conditional in the event that the number I am looking at is 0. I need to add that to all of my formulas. In a different circumstance, I could just search for "If(Countif" and replace it with "IF(C20=0,"-",IF(COUNTIF" and then manually update the references to C20 to reference the correct cell. However, If I try to do that search and replace, it fails because the formula becomes invalid as it is missing the close parenthesis ")" corresponding to the end of the new "If". If I were manually typing in the formula and did not add the close, it would suggest that correction and I could accept it. In a search and replace, it does not give that option, it just fails. So, I was wondering if I could use wildcards in the search and replace so that I could fix the front end and the back end of the formula at the same time.

I am using Office 365.

 

[Peter_SSs]

OK, well I'm not sure I can get you all the way, but see if this is any help, using multiple Find/Replace

Find: =
Replace with: #=
This stops the cell being a formula so you don't get the problem with invalid formulas

Find: 100)
Replace with: 100))

Find: #=
Replace with: =IF(D5=0,"-",
this turns it back into a (hopefully valid) formula

 

[ervierto]

Hey that's an awesome little trick!
Thanks!

 

[Peter_SSs]

Hey that's an awesome little trick!
Thanks!

You're welcome.