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