Adding cells to a maximum amount of?

[roc_ent]

I am creating an expense sheet, and when it comes to meals, some claim what they actually paid and others claim the per diem amount.

I'm having a hard time to figure out how to use the max function to give me the actual amount spent or the per diem but not going over $40.00

Here is how its set up

Cells: F22 is breakfast F23 is Lunch and F24 is dinner. These cells would be the actual amount spent on each meal and returns the total in cell M22. However, my maximum amount that can be paid out is $40.00 so example is 8.00+16.00+20.00 M22 would return 44.00 but I want it to maximize the cell amount to $40.00.

How can I write the formula for this?

Thanks in advance.

Bob

 

[Eric W]

Try:

=MIN(F22+F23+F24, 40)

 

[JoeMo]

In M22:

=MIN(SUM(F22:F24),40)

 

[roc_ent]

Thanks Eric, not certain why this was creating such a problem for me, must be the Canadian long weekend effect. lol Thanks again.

 

[Eric W]

It's a pretty simple formula, but a lot of people tend to mix up MAX and MIN for some reason. I always double check when I use them. Glad we could help.