Calculate the average price using Original Price in the excel file

[Anggara_Rahmadhani]

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.

[NIVED_NAMBIAR (NIVED N)]

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)

3. 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

Regards

Nived N

Happy Automation

[Anggara_Rahmadhani]

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?

[NIVED_NAMBIAR (NIVED N)]

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

Regards,

Nived N

Happy Automation

[Anggara_Rahmadhani]

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?

[ppr (Peter Preuss)]

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

[NIVED_NAMBIAR (NIVED N)]

I think I had to add something

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

[Anggara_Rahmadhani]

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

Study_case_Blibli.zip (22.1 KB)

Thanks in advance!

[Anggara_Rahmadhani]

Hi @NIVED_NAMBIAR
I adding for one manipulation .Replace(“.”,“”)
and its works

Thanks for your help!!!
cheers