writing formula to excel output

[KEPM23]

Hi Guys!

My Goal is to have this formula be written in the excel output and that the reference value is incremening example. G2, G3 and so on.

=IF(G2>75000000,"GCM",IF(G2>2500000,"GCM",IF(G2>500000,"RCM",IF(G2>250000,"CVA CBC",IF(G2=0,"NA","CVA COC")))))"

My issue is that i am having a parse error when i try to use totstring since i need the reference cell to be running. below is the formula which i am having parse error

"=IF(G"+ToString([RecordID]+1,0)>75000000,"GCM",IF(G2>2500000,"GCM",IF(G2>500000,"RCM",IF(G2>250000,"CVA CBC",IF(G2=0,"NA","CVA COC")))))".

Thanks,

Kris

[benakesh]

Hi @KEPM23 ,

Alteryx applies  formula to every row . Use  column name in formula  and  there is no need to create excel  cell in formula for every row  . 

[KEPM23]

Hello @benakesh,

The output that i needed is the actual formula since the output file may need some changes.

[32bit]

@KEPM23 You can't output formulas to Excel. You can to a csv, but not Excel.

[KEPM23]

@32bit yes you are right that it should be in CSV file, but i want to write is in xlsx file then we just perform a text to column just to have the formula be live in the xlsx file. 

I am following this thread below which helps me, however the formula that i am trying to write should be like this that the cell G2 must be incrementing.

=IF(G2>75000000,"DCM",IF(G2>2500000,"GCM",IF(G2>500000,"RCM",IF(G2>250000,"CVA CBC",IF(G2=0,"NA","CVA COC")))))

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Writing-Formulas-to-Excel/ta-p/1233...

[benakesh]

Hi @KEPM23 ,

Try this  : create  new excel cell  and  replace G2 with new cell  in the formula  . 

[KEPM23]

got it benakesh! thanks a lot for your help!

[JR_Rocillo]

Hi everyone, newbie here, may I know if writing an excel formula possible?

Works only in .csv, but not to .xlsx nor any Microsoft excel file types.

It appears as a string, rather than a calculated value.

Please help.

Please see sample attached.

[CharlesW]

Currently I'm working on this too. 

So far I think I've gotten closer than anyone has posted thus far.

Personally, I'd rather just do all the calculations in Alteryx and output the result. 

However for this request I've been asked to "make it live" so they can adjust values and the rest of the data will update.

One of the keys is to know what row you will be on and use that in the formula.

I'm using the tile tool, grouping and allowing it to count the rows that would appear on each sheet.

Added the formula tool to increase that number by 1 (now it matches what row it will be when it exports to excel).

Now I've tried all double, all single, only outside single quotes to see how it behaves.

It writes it as literal text if I use anything other than single quotes all the way through.  

Here is my test formula 

'=sum(C'+ToString([Tile_SequenceNum])+'+E'+ToString([Tile_SequenceNum])+')'

On the output it now looks like this

=sum(C2+E2)

Its going through the table tool, then the layout tool, and finally through the render tool to multiple tabs with unique names. (which is why I cannot use the CSV output as mentioned this already works if the output is to CSV).

So quite a lot going on. However, I think I'm very close.

At this point if I click the cell and then hit enter in the formula bar above it suddenly works.

So I'm searching for a way to make it either not do this, or maybe some other way.

Right now, you can see the green arrow in the top left indicating something is "off"

According to excel this is an Unprotected formula.....  ok that's fine.....

So Although I don't have the complete answer, maybe some of the info I have here will give somebody an idea.

UPDATE:  It appears I might be at the end of the possibilities due to how excel received the info (as text).

Still I'm going forward with this solution because it does put the right formulas, written correctly, in the right places. 

But in order to get Excel to recognize it as a formula you have to "re-enter it."

You can do that like I said above, once highlighted click the formula bar and then hit enter (that does 1).

If you have a large sheet like I do, you can use Text to Columns in the Data tab to do the entire column at once. - essentially you're not moving anything you're just re-entering the same data but now Excel sees it as a formula.

If anyone else has a more elegant or complete solution I'd be happy to hear it..

For now, I need to move onto the next piece of this project. - deadlines are approaching…..