Sep 27 2019 12:00 PM
I wrote the below nested if formula, but I'm getting no results. Could someone please take a look at it and tell me what I've done wrong or what's missing?
'= if(O2<19,239,"A",if(O2>19,240, "B",if(O2> 24,440, "C",if(O2>30,680, "D",if(O2>39,000, "E",if(O2>49,920, "F",if(O2>62,920, "G",if(O2>80,080, "H",if(O2>101,920, "I",if(O2>128960, "J",if(O2>163,800, "K",if(O2>208,000, "L"))))))))))))
Sep 27 2019 01:19 PM
Perhaps you mean
=
if(O2<19,239,"A",
if(O2<24,440,"B",
if(O2<30,680,"C",
if(O2<39,000,"D",
if(O2<49,920,"E",
if(O2<62,920,"F",
if(O2<80,080,"G",
if(O2<101,920,"H",
if(O2<128,960,"I",
if(O2<163,800,"J",
if(O2<208,000,"K","L"
))))))))))))
Sep 27 2019 01:37 PM
@Sergei Baklan - No, that changed the formula completely. The results I require must come from the greater than number.
Thank you,
Sep 27 2019 02:46 PM
IMHO, that changes formula but not result. For example, if for O2 between 19240 and 24440 formula shall return "B", you may use
...IF(O2>19240,"B",IF(O2>24440,"C"...
but exactly the same result will be with
...IF(O2<=19240,"A",IF(O2<=24440,"B",...
It doesn't matter in which direction criteria check.
Sep 27 2019 02:55 PM
And alternatively you may use formula like
=LOOKUP(O2,
{-1000,19239,24440,30680,39000,49920,62920,80080,101920,128960,163800,208000},
{"A","B","C","D","E","F","G","H","I","J","K","L"}
)
Of course, it's better not to hardcode constants but put in some ranges.
Sep 29 2019 01:49 PM
I reversed the numbers and the below actually worked except for the "1" position. Not sure why it's not returning that information,
= IF(O9>208000, "12",IF(O9>163800, "11",IF(O9>128960, "10",IF(O9>101920, "9",IF(O9>80080, "8",IF(O9>62920, "7",IF(O9>49920, "6",IF(O9>39000, "5",IF(O9>30680, "4",IF(O9> 24440, "3",IF(O9>19240, "2",”1”)))))))))))
Sep 29 2019 02:11 PM