Listing IF formulas

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.

RobbieD2020 

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).

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.