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

[jmochs3]

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.

 

[lenze]

Hi and welcome to the board!!
Maybe

=MROUND(B4*$A$5,5)

Anaylsis ToolPak required

lenze

 

[jmochs3]

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

 

[lenze]

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

lenze

 

[jmochs3]

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