Rounding

[darkpiece]

I have my workout in excel.

So I have my max in one cell. 315. For my workout, each set has a
percent of that max for the amount of reps I do.


I have it setup so my max is in cell A6. And where one of my reps are
theres a formula that looks like "=a6*.52"

that gives the 52% of 315. That gives me 163.8.


I want it to round down to 160. How do I set that up automatically into
the formula or the cell. So when I put that "=a6*.52" into the cell it
automatically rounds up or down depending on the number that it
produces.

thanks.

 

[Bob Phillips]

=ROUND(A6*52%/10,0)*10

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[joeu2004]

I have it setup so my max is in cell A6. And where one of my
reps are theres a formula that looks like "=a6*.52"
that gives the 52% of 315. That gives me 163.8.
I want it to round down to 160. How do I set that up automatically into
the formula or the cell. So when I put that "=a6*.52" into the cell it
automatically rounds up or down depending on the number that it
produces.

So you want a formula that rounds to the nearest multiple of 10.
Right? Try, for example:

=MROUND(A6*52%,10).

If MROUND is not recognized (#NAME? error), install the Analysis
ToolPak by going to Tools > Add-ins and selecting ATP.

 

[darkpiece]

Bob, yours did work.

I tried manipulating the formula for it to round to the nearest 5th but
I couldn't get it to work.


So for example, if the a6*.52 comes out to be 107, i want it to round
to 105, but if it comes out to be 108 i want it to be 110....

same for all other sets, I want the number to be a multiple of 5
instead of 10.

can anyone do that?

Thanks.

 

[JE McGimpsey]

One way:

=ROUND(A6*0.52/5,0)*5

 

[Ron Rosenfeld]

=ROUND(A6*52%/10,0)*10

or

=ROUND(A6*52%,-1)


--ron

 

[Ron Rosenfeld]

Bob, yours did work.

I tried manipulating the formula for it to round to the nearest 5th but
I couldn't get it to work.


So for example, if the a6*.52 comes out to be 107, i want it to round
to 105, but if it comes out to be 108 i want it to be 110....

same for all other sets, I want the number to be a multiple of 5
instead of 10.

can anyone do that?

Thanks.

=ROUND(A6*0.52/5,0)*5


--ron

 

[Bob Phillips]

Ah, but I was anticipating his next question )

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Ah, but I was anticipating his next question )

Actually, I figured you were giving the more general solution. But also wanted
to point out the specific one for "powers of 10" since I had not seen it posted
for a while.


--ron