Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)}>}'))
)
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)}>}'))
)
Thanks Swuehl