Ignore calculation based on cell value

[peterderrington]

Morning!
An easy one this morning (based on how clever everyone round here appears to be)....

I have a spread sheet with various calculations in it. What I would like is for a cell to simply blank that cell if another cell on that row has a specific value in it.

Example.

The Column with the specific value in is BD, the value in this column can be "True", "False", or blank.

If the cell returns "True" then I want the calculation to go ahead, however if it returns as either "False" or Blank then I want it to return Blank

An example of some of the calculations that are running in some of the columns are:

AB = {=IF(ISNUMBER(SEARCH("*",AG2:AJ2)),"Yes","No")}
AC = =IF(J2<>"",J2,"")
AF = =IFERROR(IF(AE2-AC2<>"",AE2-AC2,""),"")

I'm guessing I need another 'IF' statement at the front?

Thanks.

 

[tyija1995]

Hey,

Your guess sounds good to me - Nest an IF at the start, reference BD1 like so:

=IF(BD1=1,"put your formula here","")

NB: True = 1, False/Blank = 0

 

[peterderrington]

Just tried =IF(BD2=1({=IF(ISNUMBER(SEARCH("*",AG2:AJ2)),"Yes","No")},"") but excel thinks there is a problem with the formula.

Could it be because of the {} marks? I have to have them (pressing Ctrl + Shift + Enter) to make the original calculation to work....

 

[tyija1995]

Righto, that's an array formula so I think this will work (I'm going to change BD2=1 in to BD2=TRUE, as it's going funny on my end...

Try:

IF(BD2=TRUE,IF(ISNUMBER(SEARCH("*",AG2:AJ2)),"Yes","No"),"")

Ctrl+Shift+Entered so you get the curly braces.

 

[peterderrington]

Amazing - Thank You!

Its working great.