Result not greater than .............

[Donna]

A B
10.000 1,262.61
What is the formula for saying A * B but not greater than B * .05

 

[David Biddulph]

=MIN(A1*B1,B1*.05) or
=B1*MIN(A1,0.05)

 

[Donna]

Thanks, David. What if A is variable?

=MIN(A1*B1,B1*.05) or
=B1*MIN(A1,0.05)

 

[David Biddulph]

Whatever value you have for A1 will be used in the formula.

 

[Donna]

A could be less than or more than 10% but the result can never be more than
5% of B. I don't know the formula to limit the result to 5%.

 

[David Biddulph]

That's what you asked for, and that's what I gave you.
Did you try the formulae I gave you?

 

[JoeU2004]

A could be less than or more than 10% but the result can
never be more than 5% of B. I don't know the formula to
limit the result to 5%.

First of all, it was not clear in your initial posting that "10.000" was
intended to be interpreted as a percentage. That might be why David's
solution is not working for you. You could write:

=B1 * min(A1%,5%)

But personally, I think it would be better if you entered 10% into A1, and
formatted the cell as Percentage with the desired number of decimal places,
if necessary.

Second, I am sensing that you might not understand the MIN() function and
why it does exactly what you requested. The above is a more efficient way
of writing:

=B1 * if(A1% < 5%, A1%, 5%)

or if you prefer:

=if(B1*A1% < B1*5%, B1*A1%, B1*5%)

The latter says exactly what you wrote in your first posting, namely: "the
formula for saying A * B but not greater than B * .05"

The MIN() function returns the smallest of its arguments, which can more
than 2, by the way.

Does that help?


----- original posting -----

 

[Donna]

Thanks, Joe. That formula worked. Next time I will be more specific.