Ignore calculation based on cell value

peterderrington

New Member
Joined
Aug 6, 2019
Messages
14
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2019
Messages
14
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

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
IF(BD2=TRUE,IF(ISNUMBER(SEARCH("*",AG2:AJ2)),"Yes","No"),"")

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

Watch MrExcel Video

Forum statistics

Threads
1,129,316
Messages
5,635,521
Members
416,862
Latest member
MGDlite

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top