How to use MROUND correctly

[Dougj]

I have an excel spreadsheet I use once a year so its time to adjust for the lose of pennies in Canada.

I have read about the MROUND function but am unclear how to use it?

For example on one sheet for say "Joe" I enter a column of numbers (2 decimal places is currently used as it a cost) At the page bottom I total that column and eventually move that total to a second sheet. I want that total at the bottom of the first sheet to be rounded up or down to the nearest nickel, and displayed that way, before the value is used on the second sheet.

The formula for the total at the bottom of the first sheet is just the sum of a range of cells

=SUM(D5:D19)

How do I make that displayed total show as rounded to the nearest nickel??

Thanks Doug

[daddylonglegs]

Hello Doug, try this version

=MROUND(SUM(D5:D19),0.05)

[Dougj]

well that works exactly as I need.

Now how do I incorporate it in an IF statement if I have a negative result?
Example:

=IF(Total_mandays<>0,(Total_work_costs/Total_mandays*Vin_mandays-Vin_total_work_costs),0)
becomes
=MROUND(IF(Total_mandays<>0,(Total_work_costs/Total_mandays*Vin_mandays-Vin_total_work_costs),0),0.05)

Works fine. HOWEVER if the result is a negative value (In other words that person is owed money) then my output is #NUM! ???

Doug

[Dougj]

after further investigation it appears that the MROUND function is not the best solution when resultant values may be negative or positive as the MROUND multiple must match the resultant.

example if the multiple is 0.05 then the resultant must be poitive and if multiple = -0.05 then the resultant must be a negative number.

It turns out the ROUND & ROUNDDOWN functions worked best for my needs, using this format

=ROUNDDOWN(x/m,0)*m

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.