Forum Discussion
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 start adding them into a list, each asking a different question, I get the dreaded #Value! It must be something to do with they way I am listing them i.e. separating with a coma, because, like I say, individually they each work. This is my formula below in the list - can someone please point out the obvious to me as I'm losing the will to live!
=IF($D22<2,IF($C22=25,SUM($D22/(($BP22-56-245)*($BQ22-110-140)/1000000)),IF($C22=50,SUM($D22/(($BP22-106-245)*($BQ22-150-140)/1000000)),IF($C22=75,SUM($D22/(($BP22-106-245)*($BQ22-200-140)/1000000)))))),
IF($D22>=2,$D22<4,IF($C22=25,SUM($D22/(($BP22-56-315)*($BQ22-110-140)/1000000)),IF($C22=50,SUM($D22/(($BP22-106-315)*($BQ22-150-140)/1000000)),IF($C22=75,SUM($D22/(($BP22-106-315)*($BQ22-200-140)/1000000)))))),
IF($D22>=4,$D22<7,IF($C22=25,SUM($D22/(($BP22-56-385)*($BQ22-110-150)/1000000)),IF($C22=50,SUM($D22/(($BP22-106-385)*($BQ22-150-150)/1000000)),IF($C22=75,SUM($D22/(($BP22-106-385)*($BQ22-200-150)/1000000)))))),
IF($D22>=7,IF($C22=25,SUM($D22/(($BP22-56-455)*($BQ22-110-150)/1000000)),IF($C22=50,SUM($D22/(($BP22-106-455)*($BQ22-150-150)/1000000)),IF($C22=75,SUM($D22/(($BP22-106-455)*($BQ22-200-150)/1000000))))))
10 Replies
Hello RobbieD2020,
I would like to add just a few things to the wonderful help that you've received so far.
As mathetes and JKPieterse have pointed out, there are often more efficient and less messy ways to deal with situations like the one you have presented to us. When nesting a large number of functions together, it is very easy to miss something as simple as a single ",". And when you miss something this small, trying to fix it is like trying to find a needle in a haystack. In your nested functions, as SergeiBaklan pointed out, there is at least one missing [value_if_false]. This could be resolved with a simple ",".
You attempt to write a single logical condition as multiple conditions. For example, "IF($D22>=2,$D22<4,IF($C22=25...". The way that this is written, the IF() function reads "$D22>=2" as the logicial condition, "$D22<4" as the [value_if_true], and "IF($C22=25..." as the [value_if_false]. Instead, an appropriate way to express these multiple conditions is "IF(AND($D22>=2,$D22<4,$C22=25),...".
The use of the SUM() function in your function is not necessary. For example, "SUM($D22/(($BP22-56-245)*($BQ22-110-140)/1000000))" is the same as writing "$D22/(($BP22-56-245)*($BQ22-110-140)/1000000)".
Again, if a formula ever feels too long or confusing, then it probably is.
I agree, but missing of FALSE condition is minor thing here. Formula is incorrect at all, it looks as
=IF(a=1,b),IF(a=2,c),IF(a=3,d),IF(a=4,e)
plus some other errors.
Your current formula is combination of 4 independent IF() separated by commas:
=IF( $D22<2, IF($C22=25, SUM($D22/(($BP22-56-245)*($BQ22-110-140)/1000000)), IF($C22=50, SUM($D22/(($BP22-106-245)*($BQ22-150-140)/1000000)), IF($C22=75, SUM($D22/(($BP22-106-245)*($BQ22-200-140)/1000000)) ))) ), IF( $D22>=2, $D22<4, IF($C22=25, SUM($D22/(($BP22-56-315)*($BQ22-110-140)/1000000)), IF($C22=50, SUM($D22/(($BP22-106-315)*($BQ22-150-140)/1000000)), IF($C22=75, SUM($D22/(($BP22-106-315)*($BQ22-200-140)/1000000)) ))) ), IF( $D22>=4,$D22<7, IF($C22=25, SUM($D22/(($BP22-56-385)*($BQ22-110-150)/1000000)), IF($C22=50, SUM($D22/(($BP22-106-385)*($BQ22-150-150)/1000000)), IF($C22=75, SUM($D22/(($BP22-106-385)*($BQ22-200-150)/1000000)) ))) ), IF( $D22>=7, IF($C22=25, SUM($D22/(($BP22-56-455)*($BQ22-110-150)/1000000)), IF($C22=50, SUM($D22/(($BP22-106-455)*($BQ22-150-150)/1000000)), IF($C22=75, SUM($D22/(($BP22-106-455)*($BQ22-200-150)/1000000)) ))) )Entire logic of the formula is not defined (e.g. what shall be returned if $C22=17) plus some errors in formula, but perhaps equivalent logic could be
= $D22 / 1000000 / ($BP22 - 106 + 50*($C22=25) - 455 + 70*(($D22 < 2) + ($D22 < 4)+ ($D22 < 7)) )/ ($BQ22 - 200 + 40*($C22=25) - 150 +10*($D22 < 4) )(not tested).
Thank you SergeiBaklan
$C22 wont ever = 17 as these are from a cell where the figure is chosen from one of 3 and put in manually. I'll be adding a drop down list to this cell.
I'll certainly try your suggestion in the morning.
If no other options perhaps something like suggested formula could work.
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.
Thank you JKPieterse
Yes, you seem to be right about IF($D22>=2,$D22<4,IF(. I'll need to look at that again. Essentially, I'm trying to say that if a number in a cell ($D22) is equal to or greater than 2 and less than 4, then....blah blah blah. That's clearly the wrong way to express it.
However, the 1st and last IF commands in my string work individually but it is only when I separate them with a comma, to try and use them together, that I get the issue.
I also agree that using fixed numbers in the string is not the "best way" but even so, it should work. I'm under time constraints on this and was hoping to cut a few corners..
I'll investigate VLOOKUP, thanks.
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.
- Every Excel manual I've ever read warns against formulas like this. They're next to impossible to understand. So far as I know nobody has actually ended their life over such frustrations, and I surely hope you won't be the first.
But the solution would be to break the conditions apart and deal with them in multiple columns, perhaps hiding some of those columns if you don't need the intermediate results. Nesting functions like this is fun when it works, but--as you've discovered--not fun in the extreme when they don't work.Thank you mathetes
Yes, as I was creating this string I was beginning to realise that it wasn't the best way to do things. But I'm going on hols on Monday and was hoping to make this work before then - its just a small part of what I need to complete so I was keen just to find out the particular issue in what I have done.
