4 Replies Latest reply on Jan 16, 2018 2:47 AM by Keith Peters

# Calculation decipher help required

Hi,

Apologize in advance if I've posted in the wrong area but its my first post.

I've recently found a technique I want to use and whilst trying to work out how it was achieved I've come across a calculation that I don't understand. Is anyone able to convert this to English?

IIF((min([Subject Number])/100)% [Columns Subject]==0, [Columns Subject], (min([Subject Number])/100)% [Columns Subject])

Its based on the following Viz and is the calculation for 'Column Subject'. The part that's confusing me is:  (min([Subject Number])/100)% [Columns Subject]

• ###### 1. Re: Calculation decipher help required

Hi Keith

In proper "English" something like "if % of Columns Subject is 0 then Columns Subject else % of Columns Subject"

Hope it helps.

Regards,

Norbert

• ###### 2. Re: Calculation decipher help required

Thanks Norbert,

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.

Regards

• ###### 3. Re: Calculation decipher help required

Hi Keith,

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

(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

1 of 1 people found this helpful
• ###### 4. Re: Calculation decipher help required

Thanks Paul, that's exactly what I was looking for, now I understand.