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
"Brad" wrote:
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?
"Frank Kabel" wrote:
Hi
one way:
=MAX(IF(A11000,25,IF(A11500,50,IF(A12000,75,0)) ),IF(B10.04,25,IF(B10.05,50,IF(B10.06,75,0))))
--
Regards
Frank Kabel
Frankfurt, Germany
Brad wrote:
I would like to have a formula that would return a value based on the
following criteria
A11000 or B14% return a value of 25
A11500 or B15% return a value of 50
A12000 or B16% 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.
|