I'm on the right lines I just need to get the same answer in Excel now so I know exactly how it works it out.
1 of 1 people found this helpful
The % symbol in tableau means modulus (or remainder of a division). The excel equivalent function is mod
e.g. 5 % 2 = 1 (the remainder of 5 divided by 2 when dealing with whole numbers only is 1). The equivalent excel function is =MOD(5,2)
Alternatively 40 % 4 = 0 as 4 goes into 40 with no remainder
So the part you are asking about:
(min([Subject Number])/100)% [Columns Subject]
Could be read as "First divide the minimum [Subject Number] by 100. Next, give the remainder when this number is divided by the [Columns Subject]"
Please note that if the min([Subject Number])/100) is not an integer excel's mod function and tableau's % operator work slightly differently i.e. tableau always disregards anything after the decimal place before calculation in the modulus operator
e.g. in tableau 8.9 % 3 = 2 (i.e. 8.9 is first truncated to 8. This gives 8 % 3 = 2)
in excel MOD(8.9, 3) = 2.9 (excel includes the fractions)
So strictly speaking an exact excel equivalent function for tableau's in this instance would be:
tableau = (min([Subject Number])/100)% [Columns Subject]
excel = MOD(TRUNC((min([Subject Number])/100)), TRUNC([Columns Subject]))
or in the simplified example:
tableau = 8.9 % 3
excel = MOD(TRUNC(8.9), TRUNC(3)) TRUNC truncates the number at the decimal place in excel.
Sorry If I've over simplified things anywhere
Thanks Paul, that's exactly what I was looking for, now I understand.