Does any of yall Excell genuises know how I can force a cell to round up?
I’m working on a roofing spreadsheet and I’d like only whole numbers to be entered into the column where I order.
For instance, if I have 252′ of perimeter and I want to order drip, I divide that number by ten. The number entered into the column is 25.2. I’d like the number 26 to enter there.
If I set the parameters to show no decimal places, it would show 25. If I automatically add 1 to that number, that would solve one problem…meaning that I’d at least have the minimum number shipped.
The problem is inconsistency. The program will round up if the number is greater than .5. Therefore, I would sometimes be ordering two extra items instead of one. I really don’t want an extra roll of felt shipped…etc.
Replies
Oh...I forgot to mention...I don't mind help from non geniuses either LOL.
Use =roundup (cell, 0)
ROUNDUP
Rounds a number up, away from 0 (zero).
Syntax
ROUNDUP(number,num_digits)
Number is any real number that you want rounded up.
Num_digits is the number of digits to which you want to round number.
Remarks
If num_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.
If num_digits is 0, then number is rounded up to the nearest integer.
If num_digits is less than 0, then number is rounded up to the left of the decimal point.
Example
The example may be easier to understand if you copy it to a blank worksheet.
View ImageHow to copy an example
Note Do not select the row or column headers.
View Image
Selecting an example from Help
Hope the formatting shows up better on your screen than mine.
Anyway, cut and paste from the help file:
=Roundup (cell rounding up i.e. D1, 0)
The zero rounds up to whole numbers.
Thanks Ken.
I couldn't make any sense out of the formatting that you posted but when I searched the help file and typed in roundup, it led me to the answer.
I got it working after a half a dozen attempts. I don't understand why, but I have to type =roundup and then (a4,0). I understand the a4 part of the equation but I don't know why I have to put the comma and then a zero. I don't really need to know but I have an easier time remembering if I know the "whys".
The ,0 is the number of decimal places you want. 0 is none.
aha! That makes sense. Thanks.
Now...I have another question.
When we order shingles, we order by the square in units of 1/3. I'd love to be able to make the program roundup 35.44 to say "35 1/3". Is that possible?
Well, first of all 0.44 will not round UP to 1/3.
Other than that, I don't know of an easy way.
Jim,Yes, it can be done, and the basic formula is:=ROUNDUP(A1*A2/100*3,0)/3The A1 and A2 are the length and width of the roof, and both of the 3's are for 3 bundles per square. If you're using shingles that takes 4 bundles to make a square, use 4's in place of the 3's. By the way, you should buy "Estimating with Microsoft Excel" by Jay Christofferson. This book will show how to really harness the power of Excel for estimating. Hope this helps.
Thanks elicon. Toolfreak beat you to it but only by a few seconds. I will track down that book because I'm liking the power of this program. I started learning it last year and I'm getting a bit serious about it.
LOL Duh!
I assume you meant 35 2/3, right?Best I can figure requires two cells, can't seem to get them the work togetherA1______________B1________________C1
35.44_______=ROUNDUP(A1*3,0)___=+B1/3C1 returns 35.66 but formatted for Numbers, fractions, Upto 1 dig1t will display 35 2/3TFB (Bill)Edited to get the cell numbers alligned with the "cells"
Edited 8/2/2009 9:51 pm by ToolFreakBlue
Got it=ROUNDUP(A1*3,0)/3 where A1 is your original calculated value.Format the cell for Number/ Fractions/ Upto 1 digitTFB (Bill)
That worked!
The thing that confused me was that when I peaked into the fractions formatting box, it showed 1/4. I didn't realize that it would automatically input 1/3 if I was closer to thirds. I thought it would only do quarters.
Thanks a bunch. To me, you are a genius!
Why did Microsoft.com try to set a cookie when I opened this topic?
Is Bill Gates really watching us all the time?
and then some...
Life is not a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside, thoroughly used up, totally worn out, and loudly proclaiming<!----><!----><!---->
WOW!!! What a Ride!
Forget the primal scream, just ROAR!!!
"Some days it's just not worth chewing through the restraints"
He don't like the color of your shorts.
who wearing shorts?
That's a horrible mental image.
No wonder you need that 10' wall about your place.
basiclly, yes
Of course I am wearing shorts, I live in SW Florida ... but no panties if that was your question.