Forum Discussion

Copper Contributor

Jul 09, 2022

Solved

finding deviations from the mean

Hi everyone I am working with large amounts of current data from a photovoltaic plant and I need to find those current values that deviate from the average and therefore are producing less than t...

Formulas and Functions

mtarler
Jul 11, 2022
Valentinaampuero So I go the sample files (May-Nov of 2021). I am attaching the Nov data here. In this file I added a tab (DailyAvg) where I calculate the average for every :15 sec slot for each panel across all the days recorded. I then calculate the average (col LED) and std (col LEE) for each :15sec slot and added conditional formatting to highlight all the spots <1std (orange) and <2 std (red). Finally at the bottom I did a count for how many times that particular panel was found to be <1 STD and <2 STD on average. I hope this might be helpful and can be a template for the other months. Maybe even collate these summary counts across months for even better indicator of performance across the year.
Sample-Data-20211121_20211128.xlsx62.7 MB

Valentinaampuero

Copper Contributor

Jul 10, 2022

Thanks very much for your answer. I'm gonna check the IQR method, which a haven't heard about.

I think the values are not extrictely "outlyers" because there is not a huge difference between the current average and the minimun values (that it is supposed to be the panels that are shaded and therefore produce less current than the rest). The difference is between 10% or 30% less than the average.

Thank you very much, have a great day!

Best

mtarler

Silver Contributor

Jul 11, 2022

Valentinaampuero So I go the sample files (May-Nov of 2021). I am attaching the Nov data here. In this file I added a tab (DailyAvg) where I calculate the average for every :15 sec slot for each panel across all the days recorded. I then calculate the average (col LED) and std (col LEE) for each :15sec slot and added conditional formatting to highlight all the spots <1std (orange) and <2 std (red). Finally at the bottom I did a count for how many times that particular panel was found to be <1 STD and <2 STD on average. I hope this might be helpful and can be a template for the other months. Maybe even collate these summary counts across months for even better indicator of performance across the year.

Sample-Data-20211121_20211128.xlsx62.7 MB

Valentinaampuero
Copper Contributor
Jul 11, 2022
mtarler Wow! you did in one hour what i couldn't do in the whole weekend hahaha
I'm looking at the file right now and trying to understand the formulas and the results.

Thank you so much!
- cool2021
  Iron Contributor
  Jul 11, 2022
  Valentinaampuero please see my reply below to @mtarler
  - Valentinaampuero
    Copper Contributor
    Jul 11, 2022
    Hello, thanks for your answer. Now, if i have understood correctly the Standard deviation is not a valid measure because the data does not behave normally.
    
    Maybe the way you propose is more accurate and it should be used.
    
    Thanks