Excel - If Data Is Between X and Y Then...

[Mictain]

Hello all,

I'm trying to set up a formula as per the post subject relating to weight and price but with about 17 conditions per cell.

So I'm looking at:

If cell A1 is between 0 and 60 then show '32' in A4; if it's between 61 and 100 then show '49' in A4, if it's between 101 and 150 then show '68' in A4, etc, continuing on another 14 odd times until I reach the optimum weight / price combination!

Then I need to do the same for B1, C1, etc, all with the same criteria, to place the same pricing structure onto the next item.

Does anyone know how I can set this up as a formula, please? Or is there a better way to go about it?

All help appreciated.

Many thanks.

Neil.

 

[xlhelp]

Setup a table in a separate area such as one below

0 32
60 49
100 68
150 75?



Then in A4, type the formula
=VLOOKUP(A1,[table range from above],2,TRUE)

 

[xlbo]

you will be much better off using VLOOKUP

Set up a lookup table similar to:

0 32
61 49
151 68

If this data is in cells E1:F3 then your formula in A4 would be:

=vlookup(A1,$E$1:$F$3,2,TRUE)

extend your lookup table and change $F$3 to match et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mictain]

Wow, fast responses there!

I've had a play with what you've suggested and it looks like it's going to work out nicely, so many thanks xlhelp and xlbo

Neil.

 

[firefytr]

If you don't want to setup a table, you can use a standard LOOKUP formula instead...

=LOOKUP(A1,{0,32;61,49;101,68})

Of course if you ever want to change your price scale this may not be optimum and you would be better off creating a table which you could use dynamically, as shown above by Geoff and xlhelp.

HTH

-----------
Regards,
Zack Barresse

 

[firefytr]

Oh, and by the way, the LOOKUP function will not handle anything less than the smallest amount of the lookup array, in this case, anything less than 0 will error out.

-----------
Regards,
Zack Barresse

 

[xlhelp]

You are quite welcome.

This is quite an accomplishment for me. For the first time ever, I had faster fingers than Geoff.

 

[Mictain]

Thanks for that firefytr

The price scale will change every now and again, but what you suggested is what I was thinking of initially, so it's good to have that formula as well.

That sounds like an achievement to celebrate then, xlhelp. You need one of these...

 

[xlbo]

LOL - I can never remember if the lookup goes for a higher or lower value so I set it up in a spreadsheet to test - note to self - check responses before submitting !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Yeah, it's always pertinent to know which side of the coin Excel will fall on.

-----------
Regards,
Zack Barresse