How to Apply complex formulas in excel using uipath

Help
, ,
1

Hi , I have to apply a complex formula in my Uipath script for excel

=IFERROR(DATE([@[Attended From Year]],MONTH(DATEVALUE(P2&" 1")),1),“”)

Can some one help me how can I achieve this ?

2

@AshLearnUI

You have to use Write Cell activity and pass this formulae in double quotes into value field. Make sure that formulae should be correct.

3

did WRITE CELL activity inside the FOR EACH ROW loop helped us on this
like
–once after getting the datatable named dt from excel with read range pass that variable to FOR EACH ROW as input
–and inside the loop use WRITE CELL activity with this formula as input string between double quotes and in the range mention as (if going to place this formula in C column) then
“C”+(dt.Rows.IndexOf(row)+2).ToString

so that it will enter in all rows of that column C
Cheers @AshLearnUI

1 Like
4

@Palaniyappan yes , I have already tried that way . But its not working in uipath . Formula works perfectly fine when I try it manually .

5

This is exactly how I tried.

  1. Inside the excel application scope add the read range activity with sheet name and data table as output
  2. added for each activity
    3.Added write cell inside for each loop. sheet name and range (here added the column in which I want to apply the formula)and in input tab in double quotes copy pasted the above formula.
6

image
image637×553 15 KB

7

keep this as input in the write cell activity
“=IFERROR(DATE([@[Attended From Year]],MONTH(DATEVALUE(P2&” + “”“1"”" + “)),1),” + “”“”“” +“)”

that is surround double quotes with two double quotes on either side

Cheers @AshLearnUI

8

Thank you @Palaniyappan can you tell me why we should surround double quotes with another set of double quotes and also ‘+’

1 Like
9

Fine
double quotes if we want that to be included in our string output then we need to surround them with double quotes
and here we have used + to separate the strings in the formula and then concatenating them
hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @AshLearnUI

2 Likes
10

ah… got it . we chopping the whole formula as string and passing it

1 Like
11

exactly
Cheers @AshLearnUI

1 Like
12

@Palaniyappan Hello, could you help me with this formula, I don’t know how to add the double quotes, Thanks in advance I hope you can help me

image
image854×424 16.1 KB

13

I have a formula working in excel but not in uipath write cell activity.
=SUMIF(A1:A18,“PS”,B1:B18)
error screenshot is uploaded.

Screenshot (18)
Screenshot (18)1920×1080 217 KB

14

Hi Palani
I cant write this one
=IF(AND([@[Unit Price]]<>“”,[@Quantity]<>“”),([@[Unit Price]]*[@Quantity]),“”)’

can you help me please