Roundup

Bondiee

New member
Joined
Nov 9, 2011
Messages
6
Reaction score
0
Points
0
I need to write a formula where it rounds to the nearest whole percentage, i.e. 6.88% to 7%, or 93.12% to 93%. I then want to use that cell and the rounded percentage in future calculations. How do I tell excel to use the rounded percentage from there. HELP!
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
You can use the Excel =round() function. It takes two arguments, one is the data you want to round and the second the number of decimal places. Since you're working with percentages you'll need two decimal places to round it off to a single percentage.

Ie if your number is in cell A1 then you would use:

=round(A1,2)

Jesse
 

Bondiee

New member
Joined
Nov 9, 2011
Messages
6
Reaction score
0
Points
0
Thank you, I think I got that part. Now what if I need to first take the sum of A1/A2, i.e. =SUM(A1/A2) and round the answer to the nearest whole number?
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
Assuming that =SUM(A1/A2) returns a percentage your formula would be:

=ROUND(SUM(A1/A2),2)

Jesse
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
=ROUND(A1/A2,0)

But... I would ask yourself if you really want to round each calculation as you go through, or only the final number. By roundind each step in the process you may find that your final number isn't as mathematically accurate... depending on your purpose of course.
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
Good point Ken, unless you're rounding for significant digits it should be the very last stop in the calculation. Even at that point it would be better to use number formatting to display the significance you require.

Jesse
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Ha! Didn't realize that you posted as I was writing my reply. :)

Bondiee, for what it's worth, I wrote the exact same formula as Jesse to start with. I removed the SUM though, as it wasn't necessary. (The computation would go in the order of operations. i.e. it would divide the two numbers, giving you one number which was then summed up.)
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
Doh! I didn't even notice the sum.

Jesse
 

Bondiee

New member
Joined
Nov 9, 2011
Messages
6
Reaction score
0
Points
0
Thanks guys! That is all very helpful. You also touched on the other problem that I was having. Once I round that percentage, I need to then multiply another field by the rounded percentage. But Excel wants to round by the true calculation and not the rounded number, how do I tell it to use the rounded number in future calculations??
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
If you reference the calculation that uses =round() in another calculation it will use the rounded amount.

Jesse
 

Bondiee

New member
Joined
Nov 9, 2011
Messages
6
Reaction score
0
Points
0
Here is another question for you guys. I have the formula =(DATE(YEAR(E5), MONTH(E5), DAY(E5)+20) but I want to add to it an IF statement. I would like to say that if E5 is blank that I can have the phrase "enter date" in that field. I tried using =IF(E5>0,"Enter Date","Enter Date") but it didnt work. What am I doing wrong? Thanks again!
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
232
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
=IF(E5="","Enter date",E5+20)
should do it.
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
I think what he wants is:

=IF(ISNUMBER(E5),DATE(YEAR(E5), MONTH(E5), DAY(E5)+20,"Enter a date")

It doesn't actually check if it's a date but is functionally the same as checking the value vs 0.

Jesse
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
232
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
Is 0 not a number? ;)
All that Date stuff is still unnecessary - just add 20.
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
vs GREATER than zero :p

Good call on the date, I totally skipped over that. You're totally right, just ignore the last thing I said. Must have had some bad coffee........

Jesse
 
Top