MrExcel Publishing
Your One Stop for Excel Tips & Solutions

number formatting


Posted by Mike Sharp on November 06, 2001 11:14 AM

I coach weightlifters. On excel I put the athletes
names in Column A. I put their maximum lift in Column
B. In Column C, I figure 65% of their maximum lift;
Column D, 70%; Column E, 75%, etc. The percentages
come out in 162.5, 183.75, just odd amounts. How do I
put the percent formula and the Round formula both in
so that the numbers in my percent columns come out in
multiples of 5, which is what our weights are in?


Posted by IML on November 06, 2001 11:21 AM


If you want them to be weaker, use
=FLOOR(B2*0.65,5)
if you want them to be stronger use:
=CEILING(B2*0.65,5)

By the way, I once say a formula that estimated your max lift based amount lifted for a maximum number of reps, do you know what this is per chance?

Posted by lenze on November 06, 2001 11:23 AM

Try using th MROUND Function. You will need the Analysis Tool Pack Add-in installed.

Posted by Aladin Akyurek on November 06, 2001 12:13 PM

=MROUND(($B$2*C1),5)

is of course equivalent to

=ROUND(($B$2*C1)/5,0)*5

where I assume C1 to house 65%, D1 70%, etc. so that the formula can be copied across then down.

The advantage of the second is that you don't Analysis Toolpak. Moreover, I read somewhere but forgat the details that MROUND seems to lead to errors.

By the way, this query seems identical to one by Mona Gloff further down.

Aladin

=========