Function to Round

5 posts / 0 new
Log in or register to post comments
Last post
Thu, 10/26/2017 - 06:23
#1
Ahmed
Offline
Joined: 09/20/2017 - 05:45
Function to Round

Hi All,
I have column of duration in second and i need to round it : first to 60 and to 30 by creating two column.
Using @Round isn't very helpful for me, so ; is there any function ? or should i use @Round with another function,
Thank you.

Top
Thu, 10/26/2017 - 07:17
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Ahmed,

I am not sure I understand what you want to do, could you give me an example of the before and what you expect from the two new fields.

Thanks

Brian

Top
Thu, 10/26/2017 - 09:19
Ahmed
Offline
Joined: 09/20/2017 - 05:45

Hi Brian,
Okay, for example; i have coulmn named duration and I would like to rounded them to 30 or to 60 
in Excel i use : Ceiling  (see the file uploaded)
Thank you.
 

Files: 
File Rounde Duration.xlsx
Top
Thu, 10/26/2017 - 09:48
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Ahmed,

Thanks for the spreadsheet.  Here are the equations I came up with.

@if(@Round(DURATION_SECOND, 30) < DURATION_SECOND, @Round(DURATION_SECOND + 15, 30), @Round(DURATION_SECOND, 30))

@if(@Round(DURATION_SECOND, 60) < DURATION_SECOND, @Round(DURATION_SECOND + 30, 60), @Round(DURATION_SECOND, 60))

What I did since round always rounds up or down it doesn't work in your scenario as you want it to always round up.  So I first test to see if the result is smaller then the amount rounding, meaning that it rounded down, if it does I add half the amount (15 for 30 and 30 for 60) to it and round again to force it to round up.

Thanks

Brian

Top
Thu, 10/26/2017 - 10:10
Ahmed
Offline
Joined: 09/20/2017 - 05:45

Hi Brian,
Thank you very much. 

Top