Extracting the exact number from Excel cells with functions

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	B	C	D	E
1	Countries	Value1	Value2	Value3	Totals
2	France	100	101	102.7	304
3	Germany	200	30.2	120	350
4	Norway	102	600	20.8	723

Input

Accepted answers

Lozyee

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

All comments

neilgallen

@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

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.

A	B	C	D	E
	Customer	Item1	Item2	Total Sale
1	Customer A	$34.5	$43.3	=(c+d)*0.67

estherb47

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703