Calculate a number based on an interval

[Vitalie Ciobanu]

It seems that I can't figure it out for myself and need some help.
For example I have a random number from 0 to 1:
0.10
0.11
0.24
0.08
0.14

I also have two columns with interval numbers (min and max):
min max
0.00 0.01
0.01 0.05
0.05 0.14
0.14 0.28

And I have one column more with simple numbers:
20
40
60
80
100

Now what I need. If I will take the first random number (0.10), it matches
the third interval (0.05-0.14) and after this I have to look for the number
that corresponds to this interval, in my case it is 60.
I can't figure the formula that looks in what interval that matches the
random number. Can someone help me with a hint or with a formula?
Thank you!
--
Name: Vitalie Ciobanu
Nickname: AISBERG
Homepage: http://aisberg.rau.ro

[davesexcel]

if you could sort all your columns then you could use VLOOKUP

[Bernard Liengme]

The random values are in A2:A6
The Min values in B2:B6 (Max values not needed)
This formula =INDEX($D$2:$D$6,MATCH(A2,$B$2:$B$5,1))
returns the required 60
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Vitalie Ciobanu" <aisberg@rau.ro> wrote in message
news:eEBosT4SGHA.5736@TK2MSFTNGP10.phx.gbl...
> It seems that I can't figure it out for myself and need some help.
> For example I have a random number from 0 to 1:
> 0.10
> 0.11
> 0.24
> 0.08
> 0.14
>
> I also have two columns with interval numbers (min and max):
> min max
> 0.00 0.01
> 0.01 0.05
> 0.05 0.14
> 0.14 0.28
>
> And I have one column more with simple numbers:
> 20
> 40
> 60
> 80
> 100
>
> Now what I need. If I will take the first random number (0.10), it matches
> the third interval (0.05-0.14) and after this I have to look for the
> number that corresponds to this interval, in my case it is 60.
> I can't figure the formula that looks in what interval that matches the
> random number. Can someone help me with a hint or with a formula?
> Thank you!
> --
> Name: Vitalie Ciobanu
> Nickname: AISBERG
> Homepage: http://aisberg.rau.ro
>
>

[Biff]

Hi!

Your ranges overlap:

> min max
> 0.00 0.01
> 0.01 0.05
> 0.05 0.14
> 0.14 0.28

For example: 0.05 can't be both the max for one interval and the min for
another interval.

Try this table:

min.................interval
0........................20
0.02...................40
0.06...................60
0.15...................80
0.29..................100

Then use this formula:

A1 = random number = 0.10

=VLOOKUP(A1,H$1:J$5,2)

Where H$1:J$5 is the above table. 2 refers to the Interval column of that
table.

Biff

"Vitalie Ciobanu" <aisberg@rau.ro> wrote in message
news:eEBosT4SGHA.5736@TK2MSFTNGP10.phx.gbl...
> It seems that I can't figure it out for myself and need some help.
> For example I have a random number from 0 to 1:
> 0.10
> 0.11
> 0.24
> 0.08
> 0.14
>
> I also have two columns with interval numbers (min and max):
> min max
> 0.00 0.01
> 0.01 0.05
> 0.05 0.14
> 0.14 0.28
>
> And I have one column more with simple numbers:
> 20
> 40
> 60
> 80
> 100
>
> Now what I need. If I will take the first random number (0.10), it matches
> the third interval (0.05-0.14) and after this I have to look for the
> number that corresponds to this interval, in my case it is 60.
> I can't figure the formula that looks in what interval that matches the
> random number. Can someone help me with a hint or with a formula?
> Thank you!
> --
> Name: Vitalie Ciobanu
> Nickname: AISBERG
> Homepage: http://aisberg.rau.ro
>
>

[Vitalie Ciobanu]

to davesexcel: I can not sort the columns because they are randomly
generated and it doesn't meet my needs. Thanks anyway!

to Bernard Liengme: Thank you for answering. Although I do not understand
what the formula looks for in D2:D6.

to Biff: Your example seems to be the best! Now I need to make some changes
and make my problem work.

Thanks to all for helping me!!!! Have a nice day!

--
Name: Vitalie Ciobanu
Nickname: AISBERG
Homepage: http://aisberg.rau.ro

"Vitalie Ciobanu" <aisberg@rau.ro> wrote in message
news:eEBosT4SGHA.5736@TK2MSFTNGP10.phx.gbl...
> It seems that I can't figure it out for myself and need some help.
> For example I have a random number from 0 to 1:
> 0.10
> 0.11
> 0.24
> 0.08
> 0.14
>
> I also have two columns with interval numbers (min and max):
> min max
> 0.00 0.01
> 0.01 0.05
> 0.05 0.14
> 0.14 0.28
>
> And I have one column more with simple numbers:
> 20
> 40
> 60
> 80
> 100
>
> Now what I need. If I will take the first random number (0.10), it matches
> the third interval (0.05-0.14) and after this I have to look for the
> number that corresponds to this interval, in my case it is 60.
> I can't figure the formula that looks in what interval that matches the
> random number. Can someone help me with a hint or with a formula?
> Thank you!
> --
> Name: Vitalie Ciobanu
> Nickname: AISBERG
> Homepage: http://aisberg.rau.ro
>
>