fHow to write in a formula "if a nuber s between these two numbers

[Guest]

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

 

[Roger Govier]

Hi Freddie

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

 

[Carim]

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,B1>A2),A2,A3))

HTH
Cheers
Carim

 

[Roger Govier]

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)))

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Roger Govier]

Hi Carim

Just
=MIN(A3,MAX(A1,(INT(B1/10)*10)))
will suffice provided the pack sizes are always entered in ascending
size.

 

[Carim]

Hi Roger,

Thanks a lot for your comment ...
It is always great to keep on learning ...

Best Regards
Carim

 

[Guest]

thank you both for your input, I'll try it later tonight...

/Freddie

 

[Guest]

thank you both for your input, I'll try it later tonight...

/Freddie