returning a value based on mulitple criteria

[Brad]

I would like to have a formula that would return a value based on the
following criteria

A1>1000 or B1>4% return a value of 25
A1>1500 or B1>5% return a value of 50
A1>2000 or B1>6% return a value of 75

Here's the tricky part. I always want it to return the higher value. In
other words if the value in A1 is 1100 but the value in B1 is 5.5% then I
want it to return a value of 50.

 

[Frank Kabel]

Hi
one way:
=MAX(IF(A1>1000,25,IF(A1>1500,50,IF(A1>2000,75,0))),IF(B1>0.04,25,IF(B1>0.05,50,IF(B1>0.06,75,0))))

 

[Brad]

Thank Frank

I used this fomula and it seems to be working for picking up the first
function but not the second or third greater than. For example (my columns
are acutally I and J) I have a value of $942.99 in I and a value of 5% in J
and the formula returned a value of 25 not 50. Any ideas why?

 

[Peo Sjoblom]

There are probably smarter ways

=MAX(VLOOKUP(A1,{0,0,0;1001,0.041,25;1501,0.051,50;2001,0.061,75},3),VLOOKUP(B1,{0,0;0.041,25;0.051,50;0.061,75},2))

Regards,

Peo Sjoblom

 

[Peo Sjoblom]

I haven't tested Frank's formula but my formula returns the same,

5% (according to your post)

means greater than 5% so 5% correctly should retrun 25 and not 50
if 5% should be 50 then change the condition to >=5%
and assuming you also meant >=1000 and not >1000

My formula then should be

=MAX(VLOOKUP(A1,{0,0,0;1001,0.041,25;1501,0.051,50;2001,0.061,75},3),VLOOKUP(B1,{0,0;0.04,25;0.05,50;0.06,75},2))

Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
sorry, my fault. Should have tested this. re-order the formula as follows:
=MAX(IF(A1>2000,75,IF(A1>1500,50,IF(A1>1000,25,0))),IF(B1>0.06,75,IF(B1>0.05,50,IF(B1>0.04,25,0))))

 

[Leo Heuser]

A third possibility:

=MAX((A1>{2000,1500,1000})*{75,50,25},(B1>{0.06,0.05,0.04})*{75,50,25})