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

#### jmochs3

##### New Member
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.

### 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
Hi and welcome to the board!!
Maybe
Rich (BB code):
``=MROUND(B4*\$A\$5,5)``
Anaylsis ToolPak required

lenze

#### jmochs3

##### New Member
Where in my formula would I want to put this? Thanks for the help!

#### lenze

##### Legend
What formula?? What I posted will give you the Product of B4*\$A\$5 rounded to the nearest 5.

lenze

#### jmochs3

##### New Member
Wow. That was easy enough. It looks like I was trying to overthink Excel! Thank you for your help!

Replies
1
Views
91
Replies
2
Views
187
Replies
1
Views
162
Replies
1
Views
210
Replies
3
Views
269