Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extracting the exact number from Excel cells with functions

Lozyee
5 - Atom

Hi - I'm pulling some totals from Excel, where the total is a function of other cells. Alteryx will only input the number that is displayed and not the exact value. 

 

Does anyone have any ideas on how I can ignore how the cell is formatted and take the exact number?

 

+++

 

Example - if E2 here contains '=Sum(B2:E2)', Alteryx will pull 304 for France and not the exact value 303.7

 

 

A

BCDE

1

Countries

Value1Value2Value3Totals
2France100101102.7304
3Germany20030.2120350
4Norway10260020.8723
4 REPLIES 4
neilgallen
12 - Quasar

@Lozyee At the risk of asking a dumb question, why not just calculate the totals within the workflow instead of importing them from the excel file?

Lozyee
5 - Atom

You're absolutely right and I do that in most instances.. I used the totals example for simplicity's sake, but the calculations I'm looking at are using inputted values, which are different record to record. These values are also not recorded anywhere. 

 

A better example would be if the totals were transaction sale totals, which are then given a unique discount.

 

ABCDE
 CustomerItem1Item2Total Sale
1Customer A$34.5$43.3=(c+d)*0.67
estherb47
15 - Aurora
15 - Aurora

Hi @Lozyee 

 

Have you looked at the data types? I'm wondering if the last column is being imported as an integer rather than a double.

 

Might you be able to post a workflow so we can troubleshoot?


Cheers!

 

Esther

Lozyee
5 - Atom

Thanks Esther - looks like the issue is because the source files are in .xls format. Changing my files to .xlsx has done the trick. 

 

Realise now that Alteryx doesn't seem to like xls files - noticed that it would also retain parenthesis and commas for numbers

Labels