Capping results

I have this formula

=SUM(IF(SUM(F18)>40,SUM((SUM(F18)-40)*1.5)+40,(SUM(F18))))

but if the results are 40 or higher I want it to just display 40.

How do I do that?

Wrap your current formula in a MAX()

=MAX(SUM(IF(SUM(F18)>40,SUM((SUM(F18)-40)*1.5)+40,(SUM(F18)))),40)

Thanks, I actually needed a minimum, not a max but you set me in the right direction! Thanks.

Oops! That's right.

Glad to help by giving the opposite of what you wanted.

So ... what you're saying ... is that you *don't* want to do any
calculations ... is that correct?

=MIN(F18,40)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"djarcadian" <[email protected]> wrote
in message news:[email protected]...
>
> I have this formula
>
> =SUM(IF(SUM(F18)>40,SUM((SUM(F18)-40)*1.5)+40,(SUM(F18))))
>
> but if the results are 40 or higher I want it to just display 40.
>
> How do I do that?
>
>
> --
> djarcadian
> ------------------------------------------------------------------------
> djarcadian's Profile:
http://www.excelforum.com/member.php...o&userid=15877
> View this thread: http://www.excelforum.com/showthread...hreadid=498451
>