/docs/community?hl=en
/docs/community?hl=en
3/13/13
Original Poster
Bas Braams

Re: Is there anyway to round up?

<<Is there anyway to have the format round up>> In principle there are two ways: functions CEILING and ROUNDUP that are described, in a way, in the Google spreadsheets function list [1]. Unfortunately the descriptions in the Google spreadsheets function list are incoherent blather that express "we haven't a clue what it does, probably something as suggested by the name, you'll need to try it out". Specifically:

CEILING(number, significance, mode)

Rounds the given number to the nearest integer or multiple of significance. Significance is the value to whose multiple of ten the value is to be rounded up (.01, .1, 1, 10, etc.). Mode is an optional value. If it is indicated and non-zero and if the number and significance are negative, rounding up is carried out based on that value.

ROUNDUP(number, count)

Rounds the given number up. Count (optional) is the number of digits to which rounding up is to be done. If the count parameter is negative, only the whole number portion is rounded. It is rounded to the place indicated by the count.

(end of quotations from [1].) Make of it what you like. A better description of the presumed intent may be found in the OASIS function list [2], Section 6.17.

On top of the incoherent blather of the description the implementation of some of the rounding functions is wrong no matter the intended specification [3]. Welcome to Google Spreadsheets. For further errors see [4].

[1] Google spreadsheets function list

[2] Open Document Format for Office Applications (OpenDocument) Version 1.2

[3] (2012-07-24 and on) rounding error in google spreadsheets

[4] (2012-05-06) Errors and other issues with statistical and mathematical functions in GSheets
Community content may not be verified or up-to-date. Learn more.
All Replies (2)
Yogi Anand
3/13/13
Yogi Anand
Hi Shane:

Getting inspiration from contribution by Bas Braams, I had a little play with the use of the ROUNDUP function and the anomalies in use of CEILING function in Google spreadsheet -- it turns out Google spreadsheet, Excel and OpenOffice all yield differen results -- I have presented my findings in my following blog post:

yogi_Some Anomalies In Use Of CEILING Function Comparing Google Spreadsheet Excel And OpenOffice

so check it out to see how this works for you.

Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com


3/13/13
Original Poster
Bas Braams
Yogi's spreadsheet [1] shows anomalies in the behavior of the CEILING function that I had not noticed earlier. There are similar anomalies in the FLOOR function. After some exploration I think that the following is true. If N is a nonnegative integer (but not so large that roundoff issues further complicate matters) and if 0<x<=1e-7 then CEILING(N+x,1) returns the value N, although a return value N+1 would be expected. If 1e-7<x<=1 then CEILING(N+x,1) returns N+1 as expected. Likewise if 1<=N (and N is an integer and is not very large) then for 0<x<=1e-7 Google Sheets FLOOR(N-x,1) returns N although N-1 would be expected and for 1e-7<x<=1 FLOOR(N-x,1) returns N-1 as expected.

The issue has nothing to do with inevitable roundoff of floating point arithmetic. This is most clearly demonstrated by the example =CEILING(0.0000001,1), which returns the value 0 although a value 1 would be expected.

According to its supposed specification the functions CEILING and FLOOR can take an optional third argument, but everything that I try for the 3rd argument gives the result #N/A and an error message "Wrong number of arguments to CEILING" (or to FLOOR).

[1] yogi_Some Anomalies In Use Of CEILING Function Comparing Google Spreadsheet Excel And OpenOffice
Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.