Forum Discussion
IF statement with rounding in Excel Professional Plus 2019
I need to work out how many appointments can be fitted into an hour, at different appointment intervals but I need them rounded down to the nearest whole number. I have looked at some other posts but the one I found doesn't make sense to me; "Replace the A1 with your IF statement minus the = in your formula. =ROUND(A1/5,0)*5"
My equations need to be for 8, 12, 15, 20 minutes so:
60 minutes divide by 8 minutes multiplied by 6.5 hours = 48.75. But I need it to show 48.
=(60/8)*6.5
So I need it to be the simple equation, then and IF statement, plus the rounding....
laurenwhelan I would suggest you choose one of three functions to round down the calculated numbers. INT, TRUNC or ROUNDDOWN.
Demonstrated in the picture below. File attached for your convenience.
4 Replies
laurenwhelan I would suggest you choose one of three functions to round down the calculated numbers. INT, TRUNC or ROUNDDOWN.
Demonstrated in the picture below. File attached for your convenience.
laurenwhelan Well, you already did most of the work by writing the formula as you described, 60 / n * 6.5 where n represents the duration (8, 12, 15 or 20 minutes).
All I did was the put the values 60 and 6.5 each in its own cell so that you can reference them in a formula, rather than "hard-coding" them. Should you ever want to change the hours per day to 7.5, just change it at the top and all calculations will be updated automatically. Note some $-signs. These make sure that a reference to a cell doesn't automatically change when you drag a formula to copy it across or down. Another way to achieve that would be to use Named Ranges. Google for that term in combination with Excel to learn more about it.
When it comes to rounding, the way you described it gives you a few options. Ignoring the decimals is easily achieved by the INTeger and TRUNCate functions. And then you could also use ROUNDDOWN. As the name suggest, it rounds down to the specified number of decimals. ROUNDDOWN(48.75,0) will result in 48. Or, ROUNDDOWN(48.75,1) will become 48.7. Compare this to the regular ROUND function, where ROUND(48.75,0) will produce 49. It rounds up or down to the nearest integer.
