If formula to return a different value if a cell has 0 or is blank

GSY01

New Member
Joined
Sep 18, 2019
Messages
8
Hi experts,
I am new to the site, so I apologize if I have posted this in the incorrect area.

In the below table I have the following formula in C3-C5,

=IF(B3=1,$D$2,IF(B3=2,$E$2,IF(B3=3,$F$2,IF(B3=4,$G$2))))

A1
B
C
D
E
F
G
2


Start
Pause
Re-set
Stop
3
1
Start
1
0
1
1
4
4
Stop
1
1
1
1
5
3
Re-set
1
0
0
1

<tbody>
</tbody>


As you can see if I change the number in B3-B5 the value in C3-C5 changes.

If I change B3 to 2, C3 changes to Pause.

I am trying to come up with a formula if I change B3 to 2, it can see E3 is 0 and it automatically changes to the next cell that contains a value other than 0 in this example it would be F2 (Re-set).

If I was to use B5 as the example and entered 2 in B5 it would return Stop as E5 and F5 both have 0.

I can substitute the 0 to blank cells if that makes it easier.

Thanks,
Gavin
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,755
=IF(LEN(A1)=0,"The cell has nothing in it",IF(A1=0,"The cell has zero in it","The cell has something else in it"))
 

GSY01

New Member
Joined
Sep 18, 2019
Messages
8
Hi John,

Thanks for the reply, that didn't work how I want it.

I'm not even sure I can achieve what I want using a IF formula.

I want to be able to manually change the number in column B and the text in column C changes depending on the numerical value in D,
If D is blank it will look in E then F till a value is found, then returns the text in in headed in either E or F as a number is found.

I hope that makes sense.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,733
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Formula in C3 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
If you prefer not to have to use the Ctrl+Shift+Enter, I have provided an alternative normal-entry formula in column J

Excel Workbook
BCDEFGHIJ
2StartPauseRe-setStop
32Re-set1011Re-set
43Re-set1111Re-set
52Stop1001Stop
Next Value
 

Forum statistics

Threads
1,085,842
Messages
5,386,288
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top