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