Cumulative Average, St. Dev, %CV Function

[Jose Gregorio]

I'm building a chart that would cumulatively provide average, standard deviation, and %CV as new data is added to the chart.

\1

The formula would be in the Mean/Std. Dev/%CV columns. I want the formula to check if every cell in the same row and below as "Binary" column returns as Odd, and if yes it adds the value of the "Value" column to a grouping (array?). Once it's checked all the cels in that column, it then executes the function, either average, standard deviation, or %CV. If it returns as Even, it performs the same operations except averaging the Even number cels.

In this example, I've provided the (manual) calculated values. So at the bottom, you see the Mean for October 1 as the same as the Value for October 1, but the Mean for October 11 includes October 11, 9, 7, etc.

I did the formatting like this because I want the chart to be automated and not require any manual formula typing or copy/paste, and as mentioned earlier, be a single equation that works for cumulative data. I have a macro that automatically shifts down the row once something is typed in that "Type data here" section, so I want that single formula in the Mean/St.Dev/%CV.

I can figure out, using IF functions, for Excel to identify Odd/Even then look in the next Column over for a Value, but now how to add that data to a group and then perform a statistical function, and not how to specify the formula can't look below the row the formula is located in.

Any suggestions on how I'd solve this?

[Kaper]

As there is no answer for already some time, I'd suggest: attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Picture is basically of no use (except illustration) - nobody will type in dummy data