Hi I need to write a formula that chooses from 3 different values and returns
the one I want it to.

lets say I have 10, 20 & 30 in 3 different cells and the number I want it
to look at is 22.

I want to say that if the number I look for is below 20 return 10, if its
between 20 & 30 I want it to return 20 and if it's above 30 I want it to
return 30.


How would I write this formula or at least ifsomeone can help me with hw I
write the between 20 & 30 formula.

/Freddie

Hi Freddie

Try
=MIN(30,MAX(10,(INT(I8/10)*10)))

--
Regards

Roger Govier


"FreddieP" wrote in message
...
Hi I need to write a formula that chooses from 3 different values and
returns
the one I want it to.

lets say I have 10, 20 & 30 in 3 different cells and the number I
want it
to look at is 22.

I want to say that if the number I look for is below 20 return 10, if
its
between 20 & 30 I want it to return 20 and if it's above 30 I want it
to
return 30.


How would I write this formula or at least ifsomeone can help me with
hw I
write the between 20 & 30 formula.

/Freddie

Hi

I should have made the cell holding the number, e,g 22 A1 rather than I8
(which looks like eighteen - I happened to be in column I when I was
testing)

=MIN(30,MAX(10,(INT(A1/10)*10)))

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Freddie

Try
=MIN(30,MAX(10,(INT(I8/10)*10)))

--
Regards

Roger Govier


"FreddieP" wrote in message
...
Hi I need to write a formula that chooses from 3 different values and
returns
the one I want it to.

lets say I have 10, 20 & 30 in 3 different cells and the number I
want it
to look at is 22.

I want to say that if the number I look for is below 20 return 10, if
its
between 20 & 30 I want it to return 20 and if it's above 30 I want it
to
return 30.


How would I write this formula or at least ifsomeone can help me with
hw I
write the between 20 & 30 formula.

/Freddie

Hi Freddie,

Assuming your numbers 10 20 30 are located in cells A1, A2, A3
and that the number to be tested is in cell B1,
then in cell C1 you could have :
=IF(B1<A2,A1,IF(AND(B1<A3,B1A2),A2,A3))

HTH
Cheers
Carim

Hi Carim, thanks for your help, but if the value in cell B1 is 20 which is
the exact value of A2 it still returns A3=30.

Also some times A2 and/or A3 can be empty and ten it returns 0.
Any suggestions?

/Freddie

"Carim" wrote:

Hi Freddie,

Assuming your numbers 10 20 30 are located in cells A1, A2, A3
and that the number to be tested is in cell B1,
then in cell C1 you could have :
=IF(B1<A2,A1,IF(AND(B1<A3,B1A2),A2,A3))

HTH
Cheers
Carim

Freddie,

You can adjust your formula by adding sign = where needed ...

=IF(B1<=A2,A1,IF(AND(B1<=A3,B1=A2),A2,A3))

If A2 and/or A3 are empty what should be your result ?

Carim

It should always be the one just higher than B1, so if A3 is empty and B1 is
higher than A"2 it should be A2, if also A2 is empty it should be A1.

A3 can never be empty if A2 is empty.

The specific case concerns ordering quantitys, it should calculate up to the
closest transport pac from our vendor.

In some cases the vendor only as one or two different packsizes.

/Freddie

"Carim" wrote:


Freddie,

You can adjust your formula by adding sign = where needed ...

=IF(B1<=A2,A1,IF(AND(B1<=A3,B1=A2),A2,A3))

If A2 and/or A3 are empty what should be your result ?

Carim

Freddie,


With your numbers in the same cells A1 A2 A3, cell to be tested is B1 :

=MIN(MAX(A1:A3),MAX(MIN(A1:A3),(INT(B1/MIN(A1:A3))*MIN(A1:A3))))

Roger's formula, once adjusted, should do the job ...

HTH
Cheers
Carim