Average the data in a filtered column in Excel

Feedback Studio
, ,
1

After the filter I made in Excel, I need to use a lot of activities to average the numeric values in a column. For example, I eliminated the 0 and blank values in the column named score in the excel file that I sent in the attachment with a filter. In the remaining data table, I only want to average the values in the score field. There must be a shortcut to this. What can I do. By the way I don’t want to delete any row, because I’ll continue use the same excel file for another processes.

|Date |Cust ICostumer Answered|Score|Survey Type|
|—|—|—|—|—|
|05/01/1900 00:00:00|12083284536|Yes|4|EXPERIENCE|
|05/01/1900 00:00:00|12083284536|Yes|5|EXPERIENCE|
|05/01/1900 00:00:00|12602908592|Yes|3|EXPERIENCE|
|05/01/1900 00:00:00|12083284536|Yes|5|EXPERIENCE|
|05/01/1900 00:00:00|12602908592|Yes|0|EXPERIENCE|
|05/01/1900 00:00:00|12083284536|Yes|5|EXPERIENCE|
|05/01/1900 00:00:00|12083284536|No||EXPERIENCE|
|05/01/1900 00:00:00|12602908592|Yes|5|EXPERIENCE|
|05/01/1900 00:00:00|12083284536|Yes|3|EXPERIENCE|
|05/01/1900 00:00:00|12602908592|Yes|5|EXPERIENCE|

3

Hi @Fatma_Hacioglu,

You can use this LINQ query to get the average of the Score column.
dt_input.AsEnumerable().Average(Function(x) If(String.IsNullOrEmpty(x(“Score”).ToString()), 0, CInt(x(“Score”)))).ToString()

Note: This will return a double converted to a string
image
Where AverageScore is a String

Hope this helps!

2 Likes
5

Hope this answer works, kindly mark as solution.

Thank You! :grin:

6

In an excel file with 50000+ rows this took too long so a different solution was found. Here’s the step by step:

1- Filter Activity: 0 and I removed the empty lines.
2- Copy/Paste Range Activity: I copied the filtered data to another excel sheet.

3- Write Cell Activity: I took the average with the formula “=AVERAGE(‘TemplateSheet’!E:E)”.

This way I was able to find the result much faster.

Still, thank you for your contributions.

By the way, I can recommend your solution to those working with a small dataset :slight_smile: