Forum Discussion
IF Function Referencing Multiple & Return Blank
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.
@Abiola1
Thankyou for the reply and confirming that the formula is correct.
I was getting lost with how to apply the "" to my function.
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!
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 🙂
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.
