How do I cap a cell at a certain value if there is enough to satisfy it?

[indolence]

I have a 'items per hour' problem where I want to convert an X amount of items in one column to show a certain amount of hours in another column but not going above a maximum value.

Sample scenario: For every 25 apples Bob donates, he receives 1:00 hour of community service work. If he donated 50 apples, he would receive 2:00 hours of community service work. 3:00 hours for 75 apples, and so on. The maximum number of hours he can receive is 5:00 hours. That means he only gets credit for the first 125 apples even though he donates more.

Problem: How do I set up a formula so that the hours column does not go over 5:00 hours, despite someone donating more than 125 apples? So far I have "=(A1/25)/24" to show the amount of hours but how do I cap it to show 5:00?

Thanks. I did some searching and messing around with Excel but I can't seem to get any function to work properly.

[DonkeyOte]

You can use MIN which will take the lesser of n values

Please Login or Register  to view this content.

If you want only whole hour output wrap the A1/25 within an INT or A1/25/24 within a FLOOR to "1:00"