Forum Discussion

RobbieD2020's avatar
RobbieD2020
Copper Contributor
Jan 17, 2020

Listing IF formulas

I'm hoping someone out there can help..? I'm trying to list a number of IF formulas in one cell. If I do just the first one by itself, the formula works and I get the correct answer. However, when I...
excel
JKPieterse's avatar
JKPieterse
Silver Contributor
Jan 17, 2020

RobbieD2020 Well, I don't know what you are trying to achieve, but at least I suspect that this bit is wrong:
IF($D22>=2,$D22<4,IF(

Also, your formula contains all sorts of fixed numbers:

SUM($D22/(($BP22-56-245)*($BQ22-110-140)/1000000)

some of these seem to vary with the conditions in your IF sequence. can you perhaps setup a table which lists the condition limits in its first column and the constants in your formula in subsequent columns? That way you can use the values in your conditions to lookup (look in Help for the VLOOKUP function) the numbers in that table and constuct a much simpler formula.

mathetes's avatar
mathetes
Gold Contributor
Jan 17, 2020

JKPieterse 

 

Excellent advice to @RobbieD2020 , specifically the warning against hard-coding values into formulas. The more we can build tables and refer to them as the source for values that can change (even if "almost never") is a way to have a far more robust worksheet in general.

Resources