# Multiple IF statement that Eliminates 0 values

#### jjosey01

##### New Member
Hi, I am having a problem with a multiple IF statement and would really appreciate some help: I need to expand the formula I have used below to ignore the '0' values in columns A,B and C (from, middle and to).

 from middle to Formula used: answer MP MP MP =IF(A2=C2,A2,B2) MP 0 MP MP =IF(A2=C2,A2,B2) MP MP 0 MP =IF(A2=C2,A2,B2) MP 0 0 0 =IF(A2=C2,A2,B2) 0 0 0 MP =IF(A2=C2,A2,B2) 0 MP MP 0 =IF(A2=C2,A2,B2) MP

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

I have given an example of this above, where the From heading sits in cell A1 and reads logically from left to right, top to bottom. The whold formula is based on the dominant value - that if "from"= "to" then use the "to" value but if from does not equal to then use the "middle" value. My problem is that where there are "0" values in the from, middle or to columns I get a '0' value returned whereas I would like a "" returned whilst still retaining the 'dominant' value where there are values in the 3 columns, ignoring the '0' values.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Edit: Got it

Paste this in D2
Code:
``=IF(B2=0, "", IF(A2=C2,A2,B2))``

Drag down as necessary.

Last edited:
Hi Piktro,

Thank you so much, that is really helpful. Your formula works well except for the following cases:

In row 5 and row 3, I would like the value to show 'MP' instead of "" - is that possible at all?
 from middle to Formula used: answer MP MP MP =IF(A2=C2,A2,B2) MP MP MP =IF(A3=C3,A3,B3) MP MP MP =IF(A4=C4,A4,B4) =IF(A5=C5,A5,B5) MP =IF(A6=C6,A6,B6) MP MP =IF(A7=C7,A7,B7) MP

<tbody>
</tbody>

Thanks.

If the only 2 "answers" you're going to need are "" and MP, this would work.

=IF(AND(A2=0,B2=0,C2=0)=TRUE,"", "MP")

Hi Piktro,

Thanks so much, that works really well, I have been able to successfully apply that to my entire spreadsheet, your help is appreciated!

Cheers.

Replies
6
Views
132
Replies
1
Views
338
Replies
10
Views
356
Replies
4
Views
235
Replies
4
Views
407

1,196,297
Messages
6,014,525
Members
441,826
Latest member
roudarreza

### 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.

### Which adblocker are you using?

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

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