IF Function Referencing Multiple & Return Blank

[scolgate]

Hi,

So I'm starting my exploration into Excel and formulas so my question is basic but so far I haven't cracked it so I'm asking for a little guidance on Nested IF :)

In one cell I have the potential for 4 different conditions but I can see this expanding to more in the future. For this reason should I be looking at a difference function?

In the next cell I want to output a particular value depending on the conditions of the first cell.

This works with one exception, when the cells blank the output cells says "False" and I would like the output cell to be blank. I have tried isoblank but no joy.

Below is my current formula

=IF(D5="Example1",5,IF(D5="Example2",10,IF(D5="Example3",15,IF(D5="Example4",20,))))

Thanks for any help in advance!

[#2]

Hello,

Your formula is accurate without any error. However, the issue of blank cell is what I didnt catch well. If you want your output cell to be blank, all you need to do is use double quote. That's the way to tell Excel to return empty blank cell if the condition is False in your example.

See this below
Assume you have 50 in cell A1
=IF(A1<40,70,"")
The above formula evaluate if the value in cell A1 is less 40, if it is true, it will return 40. But in this case, it is false. So it will return an empty blank cell...

[#3]

@scolgate 

Yes, the innermost IF needs a value to return:

=IF(D5="Example1",5,IF(D5="Example2",10,IF(D5="Example3",15,IF(D5="Example4",20,""))))

[#4]

@scolgate 

For such calculations it's better to keep parameters somewhere in workbook, not within the formula. Especially if you already know the changes will be required.

Such helper data could be anywhere in the workbook, e.g. in hided sheet with such and similar data.

First formula uses the range, but that's not a good solution in case of future changes.

We may name the range and use named range as in second formula. Bit better, it's no need to modify each formula in case of changes, only named range.

Even more better use the table (with third formula), if you decrease/expand the list no changes will be required, table changes it's size automatically.

[#5]

@Abiola1

Thankyou for the reply and confirming that the formula is correct.

I was getting lost with how to apply the "" to my function.

@PChoate    

Placing the "" in the back of the function was exactly what I needed, so simple and it worked, I spent a lot of time not reaching my desire and you nailed it, thankyou!

@Sergei Baklan

Although for the moment this is not something that I thought I would need the guidance now opens up the thought of what I might want to use your recommendation for :) 

[#6]

@scolgate 

I only would like to say that is a good practice to keep source data not within the formula, but in the sheet.

You initial formula (4) is quite simple and fast to create. Now assume you have several formulas which use the same parameters. Or even with only one - if in a year or so you decided to change it, or other person will do it, most probably you spent quite a lot of time to find the formula, change it without mistakes and test to be sure it works correctly. Of course if only you have more than very formulas in your model.

Other words it costly in maintenance. You may use intermediate formula (5), but that doesn't save if number of parameters will be changed. Thus (3) looks more optimal. It's bit more costly in creation but less expensive for a life cycle.