How to mark column value found False as zero | Excel | Conditional formatting |

0
votes

I have a scenario where i want to mark the value as zero if found to be FALSE

Below is my table of data

my data in excel sheet

in above image if you see the column 4 => cell C4 as one 'FALSE' value and correspond to that B4 cell as Blank value

i need to set that B4 value to zero.

Note : need to search the column 4 to find false keyword and correspond to that the B column cell value is blank it need to be set as numeric value Zero

how to do it using conditional formatting

my below code 

=ISNUMBER(C4='FALSE' then B4 set to zero)
2
excelexcel-formulaconditional-formatting

2 Answers

0
votes

My first post, so be gentle please.

I am pretty sure that you could do that with conditional formatting, but I can think of simpler options. Is it required that you use conditional formatting, or do you need to get it done?

You could use Find & Replace functionality. Just select the range - which will be the whole column or part of it in your case, click Ctrl+H, go to the second tab (find and replace), leave the find field blank and write number 0 in replace field, then hit replace button until you feel all is done.

You could do that with a function as well, but I think that should do the job. I will check this post tomorrow as well, so feel free to comment.

0
votes

I am not sure if conditional formatting can set a value. Conditional formatting as the name suggest is used to set the "Formatting".

To address your need, here is what I would do:-

  1. Create a new column called "RollNumber" and rename the old one as "Old RollNumber"
  2. In the new column, i.e. cell B2 type the formula =IF(ISNUMBER(C2),C2,0)
  3. Copy the formula through to the bottom of the table (now this will apply to all)

Before hiding column C

  1. Hide column C this will hide the "Old RollNumber" column

After hiding the column

Hope that helps.