Help required - formula to check minimum and maximum threshold

Dear All,

I have a spreadsheet with Cell A1 being a dropdown to select a currency (GBP, EUR, USD), B1 being a value that is entered by user, C1 being a calculation (=B1*12.5%).

What I need is a formula (to go in C1) that will check the currency selected in A1 and based on that, check if the calculation in C1 falls within set minimum and maximum thresholds for the currency selected.

If GBP:
Min=50
Max=2000

If EUR:
Min=75
Max=3000

If USD:
Min=100
Max=3500

So, if the calculation results in a value less than the minimum threshold, then the min value (different depending on currecy selected) should be used and if it is more than the maximum threshold then the max value (again, different depending on currency selected) should be used, otherwise anything in between as dictated by the calculation.

I hope I have been clear, if not please ask.

Your help is appreciated, as always.

Thanks!

Originally Posted by Special-K

I'm sure there's a better way of doing this, but try this:

=IF(B1*12.5%<LOOKUP(A1,{"EUR","GBP","USD"},{50,75,100}),LOOKUP(A1,{"EUR","GBP","USD"},{50,75,100}),IF(B1*12.5%>LOOKUP(A1,{"EUR","GBP","USD"},{2000,3000,3500}),LOOKUP(A1,{"EUR","GBP","USD"},{50,75,100}),0))

Hi Special-K,

Thank you very much for this, very helpful indeed.

I had to make a slight adjustment (I think you were in a bit of a rush so a slight mistake there ) and it worked brilliantly.

=IF(B1*12.5%<LOOKUP(A1,{"EUR","GBP","USD"},{50,75,100}),LOOKUP(A1,{"EUR","GBP","USD"},{50,75,100}),IF(B1*12.5%>LOOKUP(A1,{"EUR","GBP","USD"},{2000,3000,3500}),LOOKUP(A1,{"EUR","GBP","USD"},{2000,3000,3500),B1*12.5%))

Much appreciated

Originally Posted by nflsales

=IF(A1="GBP",MAX(50,MIN(B1,200)),IF(A1="EUR",MAX(75,MIN(B1,300)),IF(A1="USD",MAX(100,MIN(B1,350)),"")))*12.5%
Try this formula or
=IF(A1="GBP",MAX(50,MIN(B1*12.5%,200)),IF(A1="EUR",MAX(75,MIN(B1*12.5%,300)),IF(A1="USD",MAX(100,MIN(B1*12.5%,350)),"")))

Hi Siva (?),

This didn't work, I will play around with it later on if I get a chance.

Thanks for your help anyway