floor and ceiling: round up and down to nearest multiple of 5

jmochs3

New Member
Joined
Nov 25, 2009
Messages
3
I have created a spreadsheet for a weight training program. In the sheet, I am using a max lift, then basing the workouts off of a percentage of the max lift.

I would like the end number to round up or down to the nearest multiple of 5. For example, have 212 round down to 210, have 213 round up to 215.

My problem is that I can't figure out what I am doing wrong. I have an IF scenario with a floor and a ceiling. Sometimes, it rounds the correct way, sometimes is does not (true and false).

Can you tell me what I am doing wrong based on the following formula:
=IF(CEILING((SUM(B4*$A$5)),0),CEILING((SUM(B4*$A$5)),5),FLOOR((SUM(B4*$A$5)),5))

If somebody needs more detail, please let me know! Thank you in advance for your help.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!
Maybe
Rich (BB code):
=MROUND(B4*$A$5,5)
Anaylsis ToolPak required

lenze
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
What formula?? What I posted will give you the Product of B4*$A$5 rounded to the nearest 5.

lenze
 

jmochs3

New Member
Joined
Nov 25, 2009
Messages
3
Wow. That was easy enough. It looks like I was trying to overthink Excel! Thank you for your help! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,523
Messages
5,523,370
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top