Rounding issue - please help

[#1]

Hello,

I have a chart which has 3 columns:

Column A which sums hours for a specific week,

Column B which sums hours for previous week

Column C which shows the difference between the 2

The problem is that is Column A is showing 20 from true figure of 20.25 , Column B is showing 27 from true figure of 26.5 yet Column C is showing (6)

Basically I want consistent rounding throughout so would want Column C to show (7)

The data is fed into the script in a inline table

Load * Inline

[Select11, Equ11,

Agency hours,"(SUM({}IF(Employstatus='999001302',(basichours+nextdayhours+othours+nextdayot+salaryhours)))+SUM({}Agy)+Sum({}AAAGY))"

and the data is shown in a table chart as follows

Expression for column A with Integer number format settings #,##0

=$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))

Expression for column B with Integer number format settings #,##0;(#,##0) i.e. to show any negative numbers as (...)

=$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum)+1)}>}'))

Expression for column C with Integer number format settings #,##0

=$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))-$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum)+1)}>}'))

I assume I need to use the FLOOR function to avoid rounding up and down variably (which presumably is causing the issue) but where do I need to add the floor command in the above expression? I have tried experimenting with the Number Format sections to no avail.

Any help would be much appreciated.

Thanks.

[swuehl · Accepted solution]

Have you tried using round() function, like this:

=

round(

$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))

)

-

round(
$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum)+1)}>}'))

)

[#3]

Thanks Swuehl