Calculation of coefficient of variation in Dynamic range with VBA

[binayraj]

I have below data structure in excel.
Name Order Date Purchage amount
Ramesh 1/1/2017 465
Ramesh 1/2/2017 6543
Ramesh 1/3/2017 34
Ramesh 1/4/2017 31
Shankar 1/2/2017 89
Shankar 1/3/2017 44
Yuvraj 1/1/2017 545
Yuvraj 1/1/2017 77
Yuvraj 1/2/2017 851
Yuvraj 1/3/2017 115
Yuvraj 1/4/2017 67
Yuvraj 1/5/2017 90
Yuvraj 1/6/2017 920

Based on above i want to create summary report as below with VBA.
Name Count >=2/1/2017 And <= 5/1/2017 Purchase Sum >=2/1/2017 And <= 5/1/2017 CV for sum>=2/1/2017 And<= 5/1/2017
Ramesh 3 6608 ??
Shankar 2 133 ??
Yuvraj 4 1123 ??


Based on Name and date criteria, Sum and count can be handle easily with Countifs and sumifs inside VBA too but for CV(Coefficient of variation of purchase amount {C Column here} in above example ), I might have to define range dynamically and avoid zeros and calculate. But have no idea for defining dynamic range. Please help me. Actually i want to use dynamic range for the purpose because i can extend some some things with dynamic range.

[dflak]

I almost didn’t respond to this since you did not attach a sample workbook. People usually will not take time to reconstruct a workbook, so it is usually best if you attach one.

Fortunately, it was easy enough to copy and paste the text into a workbook and make a workbook out of it.

Instructions on how to attach a workbook are at the bottom of this post.

I made your data into an Excel table for two reasons: Excel tables know how big they are so if you add data to them or take data from them, formulas and pivot tables based on them will still reference the exact amount of data they need.

Also Excel tables copy formulas down automatically.

Cells J2 and K2 have the range that you want to calculate.

I added some helper columns to the table.

Column D determines if the date is within the range.

Column E is the total for that person.

Column F is the average.

Column G is the standard deviation.

Then I made a pivot table based on this data (Columns M to Q).

The Amount is the Sum of the Amount Column
The Average is the Average of the Amount Column
The Standard Deviation is the Standard Deviation of the Amount Column

All these are built-in functions of a Pivot table.

Then I made a Calculated Formula in the pivot table of CV = Standard Deviation / Average.

Technically speaking, this is the sum of the Standard Deviations / sum of the Averages. So for Ramesh, it is really dividing 8810 by 15035 instead of 2202 by 3759, but since there are an equal number of standard deviations and averages for a person, it doesn’t matter.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.