Calculate average of similar rows cell in Excel


#1

Hi ,

How can we calculate the average of 2 similar rows cells in Excel using UiPath
Suppose: I have an excel and its 2 rows first cells are similar and we want the average of 2 cells beside the first cells in different excel sheet.
Input:
Input Value
A1 23.9
A2 18.7
A1 24.9
A2 16.8

Output
A1 Avg(23.9,24.9)
A2 Avg(18.7, 16.8)


#2

@somya177

please try Write cell activity with below formula
=(A2+Sheet2!A2)/2

Let me know if that works.

Thanks,
Rajesh G P


#3

this A1 and A2 are present in the same sheet and can be anywhere maybe cell A11 contains A2 and cell A1 contains A2
A1 and A2 are not cell numbers they are random item names like a1,a2,a3…


#4

any suggestion guys on this??


#5

@somya177

Read Range the sheet, and find the unique and loop those values to sum and average!

Thanks~Rajesh


#6

please elaborate …it’s urgent


#7

@somya177

Book1.xlsx (9.2 KB)
Main.xaml (20.0 KB)

please try and let me know if that works for you

Thanks~Rajesh


#8

hii,
Below is the error on running your workflow i got:
Assign : Column ‘Header1’ does not belong to underlying table ‘’


#9

and i am getting this error
Assign : Object reference not set to an instance of an object.


#10

@somya177

make sure you have Excel path updated in excel read range activity.


#11

Hii,

I used Read Range activity of the workbook .Updated the path and now I got this error.’
Assign : Column ‘Header1’ does not belong to underlying table


#12

any suggestion on below query


#13

@somya177

make sure the column name in the excel input file, in the example i provided the column A i named as Header1


#14

actually the excel is like this:
From “Heading1” column Bot has to remove “=” and fetch only the chars and no sign and for both a1 and a2 it has to calculate the average values of the values present in Column “Heading2”
image


#15

@rajeshprabhu_gp @SaranyaKishore…please help !!


#16

Hi,

I have just tried out with sample data. You can use this for reference.
Average.xaml (14.7 KB)


#17

can u share your sample data as well…


#18

Please find it.
AverageValue.zip (8.4 KB)


#19

Hi Saranya,
in my scenario,header1 contains multiple a1,a2,a3,…ath…the logic that u have provided is just for a1 and a2 to add in the collection…Please suggest how we will calculate if we have multiple time a1,a2,a3 and their corresponding values in header2


#20

Hi,

In this place of code, you can replace with Switch Activity if the number of header columns are known.

image

Thanks