Author Topic: Any way to round up on all decimals in Works Spreadsheet?  (Read 4598 times)

0 Members and 1 Guest are viewing this topic.

Offline jinxmchue

  • Hero Member
  • *****
  • Posts: 3841
  • Reputation: +114/-26
Any way to round up on all decimals in Works Spreadsheet?
« on: March 12, 2009, 11:33:25 AM »
I don't want anything to round down.  For example, I'd like 2.13 to round up to 3 and not down to 2.

Offline Eupher

  • Hero Member
  • *****
  • Posts: 24894
  • Reputation: +2828/-1828
  • U.S. Army, Retired
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #1 on: March 12, 2009, 03:20:57 PM »
This is an Excel function - not sure if Works supports it:

=ROUNDUP(cell reference,0). This converts 21.3 to 22.

You could also use the ODD and EVEN functions.
Adams E2 Euphonium, built in 2017
Boosey & Co. Imperial Euphonium, built in 1941
Edwards B454 bass trombone, built 2012
Bach Stradivarius 42OG tenor trombone, built 1992
Kanstul 33-T BBb tuba, built 2011
Fender Precision Bass Guitar, built ?
Mouthpiece data provided on request.

Offline jinxmchue

  • Hero Member
  • *****
  • Posts: 3841
  • Reputation: +114/-26
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #2 on: March 14, 2009, 12:53:46 PM »
Doesn't seem to be an equivalent function in Works Spreadsheet.  Yay, Microsquish!   :thatsright:

Offline Texacon

  • Super
  • Hero Member
  • *****
  • Posts: 12281
  • Reputation: +1239/-55
  • All The Way!
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #3 on: March 14, 2009, 01:10:20 PM »
Quote
Any way to round up on all decimals in Works Spreadsheet?

Have you tried to hire some really tiny cowboys?

 :-)

KC
  Build a man a fire and he'll be warm for a day.  Set a man on fire and he will be warm for the rest of his life.

*Stolen

Offline DixieBelle

  • Administrator
  • Hero Member
  • *****
  • Posts: 12143
  • Reputation: +512/-49
  • Still looking for my pony.....
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #4 on: March 14, 2009, 01:21:34 PM »
Have you tried to hire some really tiny cowboys?

 :-)

KC
:rofl: :rimshot:
I can see November 2 from my house!!!

Spread my work ethic, not my wealth.

Forget change, bring back common sense.
-------------------------------------------------

No, my friends, there’s only one really progressive idea. And that is the idea of legally limiting the power of the government. That one genuinely liberal, genuinely progressive idea — the Why in 1776, the How in 1787 — is what needs to be conserved. We need to conserve that fundamentally liberal idea. That is why we are conservatives. --Bill Whittle

Offline Wineslob

  • Hero Member
  • *****
  • Posts: 14430
  • Reputation: +778/-193
  • Sucking the life out of Liberty
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #5 on: March 16, 2009, 02:33:07 PM »
Sounds like the scam from "Office Space".     :tongue:
“The national budget must be balanced. The public debt must be reduced; the arrogance of the authorities must be moderated and controlled. Payments to foreign governments must be reduced, if the nation doesn't want to go bankrupt. People must again learn to work, instead of living on public assistance.”

        -- Marcus Tullius Cicero, 55 BC (106-43 BC)

The unobtainable is unknown at Zombo.com



"Practice random violence and senseless acts of brutality"

If you want a gender neutral bathroom, go pee in the forest.

Offline rich_t

  • Hero Member
  • *****
  • Posts: 7942
  • Reputation: +386/-429
  • TANSTAAFL
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #6 on: March 17, 2009, 12:26:30 AM »
From the help file in MS Works:

Use ROUND(x,NumberOfPlaces)

What is the ROUND function?

The ROUND functiona built-in calculation that you can use to create a formula gives x rounded to the specified number of places to the left or right of the decimal point.
If NumberOfPlaces is positive, x is rounded to the number of decimal places to the right of the decimal point.

If NumberOfPlaces is 0, Works rounds to the nearest integer.

If NumberOfPlaces is negative, Works rounds to the number of places to the left of the decimal point. NumberOfPlaces performs operations on up to 14 decimal places, and Works displays up to 9.

Click the cellthe intersection of a row and column that can contain text or numbers where you want to use the function.
On the Tools menu, click Easy Calc.
Click Other.
Under Select a category, click Math and Trig.
In the Choose a function box, click ROUND(x,NumberOfPlaces).
Press Insert.
Follow the instructions on your screen.
"The American people will never knowingly adopt socialism. But, under the name of 'liberalism,' they will adopt every fragment of the socialist program, until one day America will be a socialist nation, without knowing how it happened." --Norman Thomas, 1944

Offline jinxmchue

  • Hero Member
  • *****
  • Posts: 3841
  • Reputation: +114/-26
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #7 on: March 17, 2009, 11:12:17 AM »
Quote
If NumberOfPlaces is negative, Works rounds to the number of places to the left of the decimal point.

That's... confusing, but maybe it's what I'm looking for.  I'll try it.

Offline jinxmchue

  • Hero Member
  • *****
  • Posts: 3841
  • Reputation: +114/-26
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #8 on: March 17, 2009, 11:17:13 AM »
Have you tried to hire some really tiny cowboys?

 :-)

KC

Rawhide!!!!

Offline jinxmchue

  • Hero Member
  • *****
  • Posts: 3841
  • Reputation: +114/-26
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #9 on: March 19, 2009, 08:30:18 AM »
Didn't work.  Microsoft sucks.

Offline Eupher

  • Hero Member
  • *****
  • Posts: 24894
  • Reputation: +2828/-1828
  • U.S. Army, Retired
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #10 on: March 19, 2009, 08:35:58 AM »
Been meaning to ask - Excel is practically everywhere these days. I believe it's a fairly standard option when buying a new computer.

Why no Excel?
Adams E2 Euphonium, built in 2017
Boosey & Co. Imperial Euphonium, built in 1941
Edwards B454 bass trombone, built 2012
Bach Stradivarius 42OG tenor trombone, built 1992
Kanstul 33-T BBb tuba, built 2011
Fender Precision Bass Guitar, built ?
Mouthpiece data provided on request.

Offline LC EFA

  • Hickus Australianus
  • In Memoriam
  • Hero Member
  • *****
  • Posts: 4527
  • Reputation: +414/-33
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #11 on: March 19, 2009, 04:35:57 PM »
Been meaning to ask - Excel is practically everywhere these days. I believe it's a fairly standard option when buying a new computer.

Why no Excel?

Many machines that are intended for the "home" market have the works package in lieu of the "ms office" package included with machines intended for business.

I believe this is due to the reduced OEM licensing costs to the distributer. 


Offline rich_t

  • Hero Member
  • *****
  • Posts: 7942
  • Reputation: +386/-429
  • TANSTAAFL
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #12 on: March 19, 2009, 04:39:56 PM »
My current PC (I bought it about a year ago) came with both Office and Works installed.
"The American people will never knowingly adopt socialism. But, under the name of 'liberalism,' they will adopt every fragment of the socialist program, until one day America will be a socialist nation, without knowing how it happened." --Norman Thomas, 1944

Offline GOP Congress

  • Hero Member
  • *****
  • Posts: 2334
  • Reputation: +274/-113
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #13 on: March 19, 2009, 09:02:13 PM »
Back to your question at hand, let me preface everything by saying that this function works on Excel and Google Docs. I'm not sure if it works on Works (pun unintended), but I'm pretty sure it will as this is a basic spreadsheet function and requirement.

First of all, I understand that your requirement is to "round" all numbers with decimals fractions UP to the next integer. Please note that I actually used the term "round" based on your question. Technically, though, rounding has been defined as rounding either UP or DOWN, depending on which integral value was closer, up or down; with the halfway value rounded UP, even though technically it is the same distance from the lower integer value. To wit:

2.0 rounds to 2
2.01 rounds to 2
2.2 rounds to 2
2.49 rounds to 2
2.5 rounds to 3
2.8 rounds to 3

However, your question indicates that

2.0 "rounds" to 2
2.01 "rounds" to 3
2.2 "rounds" to 3
2.49 "rounds" to 3
2.5 "rounds" to 3
2.8 "rounds" to 3

That is the fundamental difference of the functions. In fact, the two excel / google docs formulas are as follows:

ROUND(number)

and

INT(number)

ROUND performs exactly in the first example; ie, rounding up or down, depending on how close it is to the upper or lower integer value.

INT, however, actually "truncates" the fraction off of the number, no matter how high it is. Therefore,

2.0 truncates to 2
2.01 truncates to 2
2.2 truncates to 2
2.49 truncates to 2
2.5 truncates to 2
2.8 truncates to 2

This is closer. At first glance, it appears that the problem is solved; just add 1 to the result and come up with the rounded number. But 2.0 should not have a number added. Therefore, we need to check to see if there is a fractional value present on the number. Therefore, we have to introduce one more function into the mix, the IF function.

Basically, the IF function evaluates an expression. If an expression is true, the function will process one statement. If the expression is false, then the function will process another statement.

Therefore, going back to the earlier numbers, let's check the expressions above

INT(2.0) = 2.0 evaluates to TRUE
INT(2.01) = 2.01 evaluates to FALSE
INT(2.2) = 2.2 evaluates to FALSE
INT(2.49) = 2.49 evaluates to FALSE
INT(2.5) = 2.5 evaluates to FALSE
INT(2.8) = 2.8 evaluates to FALSE

Now for the IF function. The format is like this:
IF("expression", "result if expression is true", "result if expression is false")

Here is the IF function in use. If the expression is true, the color is red. Otherwise, it's blue.

IF(INT(2.0) = 2.0, "RED", "BLUE")  evaluates to RED
IF(NT(2.01) = 2.01, "RED", "BLUE")  evaluates to BLUE
IF(NT(2.2) = 2.2, "RED", "BLUE")  evaluates to BLUE
IF(NT(2.49) = 2.49, "RED", "BLUE")  evaluates to BLUE
IF(NT(2.5) = 2.5, "RED", "BLUE")  evaluates to BLUE
IF(NT(2.8) = 2.8, "RED", "BLUE")  evaluates to BLUE

You can use mathematical functions as True / False statements as well. So to finish off (assume your number is located in cell A1):

IF(INT(A1) = A1, A1, A1+1) 

That should do the trick for you. Substitute A1 for other cell values.
"The main purpose of the Democrat Party and the Left is to destroy the United States, transform Western Civilization to a tribal-based dystopia, and to ultimately kill all conservatives and non progressives." - Jonah Kyle

Offline jinxmchue

  • Hero Member
  • *****
  • Posts: 3841
  • Reputation: +114/-26
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #14 on: March 20, 2009, 10:20:28 AM »
Been meaning to ask - Excel is practically everywhere these days. I believe it's a fairly standard option when buying a new computer.

Why no Excel?

Because the computer is several years old and came with the MS spreadsheet.  It's nothing major or important.  I use spreadsheet for just one thing.  I was just curious if it was possible.

Offline Baruch Menachem

  • In a handbasket, heading to a warm destination
  • Hero Member
  • *****
  • Posts: 1019
  • Reputation: +37/-18
  • do the best you can with what you can
Re: Any way to round up on all decimals in Works Spreadsheet?
« Reply #15 on: May 16, 2009, 03:17:59 PM »
can you do something like round(number+.49)?

That way if you have anything that is over .o2 it will round up.  Which is good enough for Government Works.
An optimist sees the glass as half full, a pessimist sees the glass as half empty, an engineer sees that there is twice the glass required to contain the beer

My name is Obamandias, King of Kings, 
  Look on my Works, ye Mighty, and despair!