11 Replies Latest reply on May 11, 2018 10:10 AM by Okechukwu Ossai

# how to convert excel formula to tableau

Hi every one. I need help urgent.

We have this excel formula in our data base, but I could not reproduce this formula bellow in tableau's calculate field. Can someone help me?

=IF(IF(IF(AND(IF([ORIGEM]="1";"SIM";IF([ORIGEM]="6";"SIM";"NÃO"))="SIM";IF([UF_CLIENTE]="PR";IF([UNIDADE]="10";"SIM";"NÃO");"NÃO")="SIM";IF(OR([TIPO_NOTA]="1";[TIPO_NOTA]="N";[TIPO_NOTA]="2";[TIPO_NOTA]="3");"SIM";"NÃO")="SIM");"SIM";"NÃO")="SIM";(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4)-9.25%*(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4);0)>0;IF(IF(AND(IF([ORIGEM]="1";"SIM";IF([ORIGEM]="6";"SIM";"NÃO"))="SIM";IF([UF_CLIENTE]="PR";IF([UNIDADE]="10";"SIM";"NÃO");"NÃO")="SIM";IF(OR([TIPO_NOTA]="1";[TIPO_NOTA]="N";[TIPO_NOTA]="2";[TIPO_NOTA]="3");"SIM";"NÃO")="SIM");"SIM";"NÃO")="SIM";(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4)-9.25%*(([PERC_ICMS]-([PERC_ICMS]*[RED_ICMS]%))-4);0)%*[BASE_ICMS];0)

THX for any help.

• ###### 1. Re: how to convert excel formula to tableau

I do not believe that anyone will be able to take that formula and output what you are looking for - you will likely need to take some time to recreate the functionality you desire.

You'll need to understand formulating calculations in Tableau: https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#functions_operators.html%3FTocPath%3DDesign%2520Views%…

This article on "Tableau for Excel users" might also be useful: https://www.theinformationlab.co.uk/2012/03/27/tableau-for-excel-users-part-2-calculated-fields/

Hope this gets you in the right direction!

• ###### 2. Re: how to convert excel formula to tableau

Wow what a formula! and not sure how you've ended up with an Excel formula in a database!

While I won't attempt to unpick this, the main difference in Syntax between Excel IF statements and Tableau's are that Tableau uses the form

IF<condition>THEN<do this if condition is true>ELSE <do this if condition is false>END (using those key words)

and you can also do further ELSEIF rather than need to nest multiple IFs

so IF...THEN...ELSEIF...ELSEIF...ELSE...END

whereas Excel uses IF(<condition>,<do this if condition is true>,<do this if condition is false>)

Also in Tableau you put the AND/OR between multiple conditions, rather than use AND and then list the conditions (separating them with a comma, or semi-colon depending on your regional settings), so something like

IF [Sales] >0 AND [profit]<0 THEN...

Hopefully this will help you reproduce your statement in Tableau form...best of luck!

• ###### 3. Re: how to convert excel formula to tableau

Hi Renato,

This seems like mission impossible but I'm happy to take a ride!

The formula is very complicated. You are in a better position to understand it since you have access to the spreadsheet or database. I've done my best to unpack the code into tiny pieces. My main aim is not to deliver a perfect solution but at least provide a good foundation to nudge you in the right direction towards achieving your goal. You would have noticed that named cells were used in the Excel formula instead of direct cell references. So, you need to find out what those named cells mean.

Another important observation is the use of '%' in the code. Tableau will not accept that. I don't even know what that means. I've left them in the codes but you must remove them or replace them with the appropriate value before implementing the codes in Tableau. If the '%' signs don't have any significance then delete them, but if they mean real percentage then replace them with ( * 0.01)

The intimidating formula can be broken down into these smaller chunks.

Step 1: Create calculated field [Code 1]

IF [ORIGEM] = "1" OR [ORIGEM] = "6" THEN "SIM" ELSE "NÃO" END

Step 2: Create calculated field [Code 2]

IF [UF_CLIENTE] = "PR" THEN

IF [UNIDADE] = "10" THEN "SIM"

ELSE "NÃO" END

ELSE "NÃO" END

Step 3: Create calculated field [Code 3]

IF [TIPO_NOTA] = "1" OR [TIPO_NOTA] = "N" OR [TIPO_NOTA] = "2" OR [TIPO_NOTA] = "3" THEN "SIM" ELSE "NÃO" END

Step 4: Create calculated field [Code 4]

IF [Code 1] = "SIM" AND [Code 2] = "SIM" AND [Code 3] = "SIM" THEN “SIM" ELSE "NÃO" END

Step 5: Create calculated field [Code 5]

([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]%)) - 4) - 9.25% * (([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]%)) – 4

Step 6: Create calculated field [Code 6]

IF [Code 4] = "SIM" THEN [Code 5] ELSE 0 END

Step 7: Create calculated field [Code 7]

[Code 6]% * [BASE_ICMS]

Step 8: Create calculated field [Final Code]

IF [Code 6] > 0 THEN [Code 7] ELSE 0 END

Remember to delete all '%' signs or replace them with the appropriate values.

I hope this works or at least helps you in understanding the internal structure of the formula. With this understanding, I expect that you can easily tweak your way to the solution you desire.

Ossai

2 of 2 people found this helpful
• ###### 4. Re: how to convert excel formula to tableau

Bravo Ossai!...very impressive

with regards the % sign...if memory serves me correctly (not really used Excel for a while) the % sign just changes that value into a percentage, so /100 would do the same thing. % is used in Tableau, as the Modulus Operator (so returns the remainder of a division), for example 3%2 = 1 (in Excel this would be =MOD(3,2)

• ###### 5. Re: how to convert excel formula to tableau

Thanks Simon. I thought as much regarding the % sign but couldn't make my mind up.

Renato,

It's most likely the % signs are percentages, so make the following changes to [Code 5] and [Code 7].

Step 5: Create calculated field [Code 5]

([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]*0.01)) - 4) - 9.25*0.01 * (([PERC_ICMS] - ([PERC_ICMS] * [RED_ICMS]*0.01)) – 4

Step 7: Create calculated field [Code 7]

[Code 6]*0.01 * [BASE_ICMS]

Hope this helps.

Ossai

• ###### 6. Re: how to convert excel formula to tableau

Thanks for taking a time with my problem. Your answer was very valuable.

• ###### 7. Re: how to convert excel formula to tableau

Thanks for taking a time with my problem. Your answer was very valuable.

• ###### 8. Re: how to convert excel formula to tableau

Thanks for taking a time with my problem. Your answer was very valuable.

• ###### 9. Re: how to convert excel formula to tableau

Thanks for taking a time with my problem. Your answer was very valuable.

• ###### 10. Re: how to convert excel formula to tableau

Thanks for taking a time with my problem. Your answer was very valuable.

• ###### 11. Re: how to convert excel formula to tableau

You're welcome Renato. Glad it helped.