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.
Bookmarks