Multiple IF statement that Eliminates 0 values

[jjosey01]

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.

Could anybody please help with a way around this?

Thank you in advance.

 

[Piktro]

Edit: Got it

Paste this in D2

=IF(B2=0, "", IF(A2=C2,A2,B2))

Drag down as necessary.

 

[jjosey01]

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.

 

[Piktro]

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")

 

[jjosey01]

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.