Hello, I have 12 different values I would like to return based on a range of numbers. Right now, I have it set up to display the 7 most common of them through nested if statements via this formula: =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1))))))) How can I include the missing values using a different function? I had read to use either an index or lookup function, but don't know how to apply these. The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90, 90-109:1.00, 250-299:1.90, >=300:2.00. Any assistance would be highly appreciated. thanks! Yoshi

try this =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1..6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1))))))) define the above formula, go to insert | names | define | refers to cell where u have the formula | name it as formula1 | ok similary define for other forumlas... then use =if(formula1,formula1,if(formula2,formula2,if(........................))) On Sep 30, 1:10 am, yoshi <> wrote: > Hello, > > I have 12 different values I would like to return based on a range of > numbers. Right now, I have it set up to display the 7 most common of them > through nested if statements via this formula: > > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1))))))) > > How can I include the missing values using a different function? I had read > to use either an index or lookup function, but don't know how to apply these. > > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90, > 90-109:1.00, 250-299:1.90, >=300:2.00. > > Any assistance would be highly appreciated. > > thanks! > Yoshi

You can use VLOOKUP with last parameter as TRUE... Set up your ranges (in ascending order) and corresponding value to get in two adjacent columns COL H COL I 0 0.7 49 0.9 89 1 109 1.9 300 2 and use VLOOKUP with TRUE in B1 and copy down ... =VLOOKUP(A1,H:I,2,TRUE) A1 should have values 0-300+ You will get the following result 0 0.7 23 0.7 49 0.9 88 0.9 89 1 108 1 109 1.9 110 1.9 298 1.9 299 1.9 300 2 330 2 "muddan madhu" wrote: > try this > > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1..6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1))))))) > > define the above formula, go to insert | names | define | refers to > cell where u have the formula | name it as formula1 | ok > > similary define for other forumlas... then > > use > =if(formula1,formula1,if(formula2,formula2,if(........................))) > > > > On Sep 30, 1:10 am, yoshi <> wrote: > > Hello, > > > > I have 12 different values I would like to return based on a range of > > numbers. Right now, I have it set up to display the 7 most common of them > > through nested if statements via this formula: > > > > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1))))))) > > > > How can I include the missing values using a different function? I had read > > to use either an index or lookup function, but don't know how to apply these. > > > > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90, > > 90-109:1.00, 250-299:1.90, >=300:2.00. > > > > Any assistance would be highly appreciated. > > > > thanks! > > Yoshi > >

Index / match tends to work better... In A1:B11 add 0 0.7 50 0.9 90 1 110 1.2 130 1.4 140 1.5 150 1.6 170 1.7 210 1.8 250 1.9 300 2 Now use the formula (In cell E1 or ???) =INDEX($B$1:$B$11, MATCH(D1, $A$1:$A$11, TRUE)) Where you put the amount in Cell D1 -- HTH... Jim Thomlinson "yoshi" wrote: > Hello, > > I have 12 different values I would like to return based on a range of > numbers. Right now, I have it set up to display the 7 most common of them > through nested if statements via this formula: > > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1))))))) > > How can I include the missing values using a different function? I had read > to use either an index or lookup function, but don't know how to apply these. > > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90, > 90-109:1.00, 250-299:1.90, >=300:2.00. > > Any assistance would be highly appreciated. > > thanks! > Yoshi