Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Function like "ceiling" in excel.
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Function like "ceiling" in excel.
 Login/Join
 
Silver Member
posted
Hi I've tried to search the forum on this subject but kept coming up short on a good solution. If someone can tell me where to find this or help with an example that would be great.

I need to take a calculated field and round up to the next multiple of 10. This can be achieved in excel using the ceiling function - is there a similar function in WebFocus? An example of what I'm looking for is this:

Number = 123
Should round up to 130 - it does need to always round up to the next highest multiple of 10.

This message has been edited. Last edited by: Kerry,


WEBFOCUS 7.14
WEBFOCUS.8.04
 
Posts: 44 | Registered: March 30, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Cathy,

No dedicated function to my knowledge but you can always grow your own -
DEFINE FUNCTION CEILING(VALUE/D20)
  CEILING/D20 = INT((VALUE / 10) + .95) * 10;
END

-* Now use it
TABLE FILE EMPDATA
PRINT COMPUTE BASENUM/D9 = ABS(RDNORM('D9') * 100);
      COMPUTE NEWVAL/D9 = CEILING(BASENUM);
   BY FIRSTNAME NOPRINT
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  GRID=OFF, SIZE=11, FONT=ARIAL, $
ENDSTYLE
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
Wow that's great. Thanks so much worked like a charm Smiler


WEBFOCUS 7.14
WEBFOCUS.8.04
 
Posts: 44 | Registered: March 30, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Cathy --

Can the underlying value ever be negative?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
In this particular case no it would not. But would be interested if there is addtional coding to handle such an instance??


WEBFOCUS 7.14
WEBFOCUS.8.04
 
Posts: 44 | Registered: March 30, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
DEFINE FUNCTION CEILING(Number/D12.2, Significance/D12.2)
-*
-* Mimics Excel's CEILING function:
-*  CEILING(number,significance)
-*  Returns number rounded up, away from zero, to the nearest multiple of
-*  significance.  For example, if you want to avoid using pennies in your
-*  prices and your product is priced at $4.42, use the formula
-*  =CEILING(4.42,0.05) to round prices up to the nearest nickel.
-*
-* The Excel function requires the two argument to be of like sign.  This WebFocus function definition 
-* relaxes that requirement:  The arguments may be of same or opposite sign, with a result equal to the first
-* argument, rounded away from zero to an integral multiple of the second argument.
-*
   Multiple /D12   = INT( Number / Significance ) ;
   Adjust   /D12.2 = ( Multiple * Significance NE Number ) * ( ABS (Number) / Number );
   CEILING  /D12.2 = 
    IF (Significance EQ 0) THEN Number
    ELSE (Multiple + Adjust) * Significance ;
END


Buiding on Tony's sample code, to illustrate how it handles positive/zero/negative values:

-DEFAULT &SIGNIF= 10

TABLE FILE EMPDATA
PRINT 
	  FIRSTNAME 
  COMPUTE BASENUM/D20.4 = 
  	IF LAST FIRSTNAME EQ ' ' THEN 0 
  	ELSE ABS(RDNORM('D9') * 100) - 100;

  COMPUTE NEWVAL/D20.4 = CEILING(BASENUM,&SIGNIF); 

   BY TOTAL HIGHEST NEWVAL SKIP-LINE NOPRINT
   BY TOTAL HIGHEST BASENUM NOPRINT 
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  GRID=OFF, SIZE=11, FONT=ARIAL, $
ENDSTYLE
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Tony A:
Cathy,

No dedicated function to my knowledge but you can always grow your own -
DEFINE FUNCTION CEILING(VALUE/D20)
  CEILING/D20 = INT((VALUE / 10) + .95) * 10;
END

-* Now use it
TABLE FILE EMPDATA
PRINT COMPUTE BASENUM/D9 = ABS(RDNORM('D9') * 100);
      COMPUTE NEWVAL/D9 = CEILING(BASENUM);
   BY FIRSTNAME NOPRINT
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  GRID=OFF, SIZE=11, FONT=ARIAL, $
ENDSTYLE
END

T


Quoting an old one, I know... I'm not following why we want to divide and multiply by 10. This seems to do what I want:

-*CEILING(2.99) = 3
-*CEILING(2.01) = 3
DEFINE FUNCTION CEILING(VALUE/D20)
  CEILING/D20 = INT(VALUE + 0.99);
END


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 155 | Registered: May 19, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Shingles

There are so many ways to accomplish the same result in WebFOCUS. If this works for you then go with it...


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1569 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
The original request was to round up to the next 10. The results from your example should both be 10, not 3.

Jack's function is elegant in that it allows you to set the significance.


WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
 
Posts: 116 | Location: Seattle, WA | Registered: April 07, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Eight years back -- that must have been while at Chubb.

I still tend to err on the side of elegance and generality.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hey Folks,
So I was doing a little more work with Mimics code up there. It is not treating negative numbers correctly. So I went ahead and modified it a bit, and I also created a FLOOR function. Both of these spit out the same values that excel spits out.

DEFINE FUNCTION CEILING(Number/D12.2, Significance/D12.2)
-* CEILING(81.46, 10) = 90
-* CEILING(-20.46, 10) = -20
   Multiple /D12   = INT( Number / Significance ) ;
   CEILING  /D12.2 = 
    IF (Significance EQ 0) THEN Number
	ELSE IF ( Number ) LT 0 THEN Multiple * Significance 
	ELSE IF ( Multiple * Significance EQ Number ) THEN Number
    ELSE (Multiple + 1) * Significance ;
END

DEFINE FUNCTION FLOOR(Number/D12.2, Significance/D12.2)
-* FLOOR(82, 10) = 80
-* FLOOR(-22, 10) = -30
   Multiple /D12   = INT( Number / Significance ) ;
   FLOOR  /D12.2 = 
    IF (Significance EQ 0) THEN Number
	ELSE IF ( Multiple * Significance EQ Number ) THEN Number
	ELSE IF ( Number ) LT 0 THEN (Multiple - 1)* Significance 
    ELSE Multiple * Significance ;
END


-DEFAULT &SIGNIF= 10

TABLE FILE EMPDATA
PRINT 
	  FIRSTNAME 
  COMPUTE BASENUM/D20.4 = 
  	IF LAST FIRSTNAME EQ ' ' THEN 0 
  	ELSE ABS(RDNORM('D9') * 100) - 100;

  COMPUTE CEILING_VAL/D20.4 = CEILING(BASENUM,&SIGNIF); 
  COMPUTE FLOOR_VAL/D20.4 = FLOOR(BASENUM,&SIGNIF); 

   BY TOTAL HIGHEST CEILING_VAL SKIP-LINE NOPRINT
   BY TOTAL HIGHEST BASENUM NOPRINT 
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  GRID=OFF, SIZE=11, FONT=ARIAL, $
ENDSTYLE
END


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 155 | Registered: May 19, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Function like "ceiling" in excel.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.