returning a value based on mulitple criteria
 

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.

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.

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.

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

"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.

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.

Hi
sorry, my fault. Should have tested this. re-order the formula as follows:
=MAX(IF(A12000,75,IF(A11500,50,IF(A11000,25,0)) ),IF(B10.06,75,IF(B10.05,50,IF(B10.04,25,0))))

--
Regards
Frank Kabel
Frankfurt, Germany
"Brad" schrieb im Newsbeitrag
...
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?

A third possibility:

=MAX((A1{2000,1500,1000})*{75,50,25},(B1{0.06,0. 05,0.04})*{75,50,25})

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Brad" skrev i en meddelelse
...
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.