Calculate the average price using Original Price in the excel file

Hi All,
I need to calculate using the column Original Price and show calculate price with the highlight the row with yellow colour if the price is below average
price. Is there a neat way of doing this?

Thanks in advance.

Hi @Anggara_Rahmadhani

You can use this way

  1. Read the excel file using read range and store in dt1

  2. use the below assign activity

average= dt1.AsEnumerable().Sum(Function(row) CDbl(row(“Original Price”)))/(dt1.Rows.Count)

  1. Now use for each loop to loop through each row of dt1

inside the for each loop, check the following condition with if activity:

CDbl(row(“Original Price”))<average

if the condition is true, then in Then section, use set color activity to set the color at specific cell with color “Yello”

Hope this logic helps you


Nived N

Happy Automation

Hi @NIVED_NAMBIAR Thanks for the reply,
My first workflow is foreach to get directory files just format .xlsx
I follow your step but I got something error that i’m not understand, the type argument in foreach using String… what should I do?

Hi @Anggara_Rahmadhani
Check this workflow
test.xaml (9.0 KB)
data.xlsx (8.2 KB)


Nived N

Happy Automation

Hi @NIVED_NAMBIAR Thanks for reply,
your step its working for using the data table that u create.
But when im running on my workflow, I got some different error because my Original Price column is string “Rp1.000.000”, any insight?

we can grap the amount with the help of a regex:

I think I had to add something

Please check this updated workflow
test.xaml (9.1 KB)

Hi @NIVED_NAMBIAR Thanks for reply,

I know the Replace work for, when I use the string manipulation like Replace… it just a remove the value that I want “Rp” , but I got the same error.

Here my screenshot the error and I attach my workflow (22.1 KB)

Thanks in advance!

I adding for one manipulation .Replace(".","")
and its works

Thanks for your help!!!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.