Not More Than or Maximum in a formula

[Ed]

Hopefully fourth time's the charm on trying to post this question:

Is there a way to have excel limit a sum to not more than ____? The
formula as it stands is =IF(AND(C16="Pharmacy",ISNUMBER
(X16),D16>5),D16-5). To that final equation, I'd like the sum to
result in a maximum or ceiling of $30. I've tried it with MAX a few
different ways so far, (e.g., ... MAX(D16-5, 30)) but no luck.

 

[Lars-Åke Aspelin]

Hopefully fourth time's the charm on trying to post this question:

Is there a way to have excel limit a sum to not more than ____? The
formula as it stands is =IF(AND(C16="Pharmacy",ISNUMBER
(X16),D16>5),D16-5). To that final equation, I'd like the sum to
result in a maximum or ceiling of $30. I've tried it with MAX a few
different ways so far, (e.g., ... MAX(D16-5, 30)) but no luck.

Try this formula:

=MIN(D16-5,30)

Hope this helps / Lars-Åke

 

[Ed]

Try this formula:

=MIN(D16-5,30)

Hope this helps / Lars-Åke

Perfect!

Now if someone can figure out why I wasn't allowed to post this
question directly on Microsoft Online....?

 

[Joel]

You need an if statement

=if(A1> 24, 24, A1)

 

[JoeU2004]

You need an if statement
=if(A1> 24, 24, A1)

=min(24,A1)