VBA code to Find Average

1. VBA code to Find Average

Hi All,

I have table data as below. There are 3 column.
Vol1
CNT
CNT_WISE_AVERAGE

I want to write a VBA code to display the average value from "Vol1" column, based on "CNT value, and put the average value in "CNT_WISE_AVERAGE" column.

There are nore then 5000 rows, having CNT values upto 80.

Please find attached excel for sample data.

2. Re: VBA code to Find Average

VBA?

Formula in C2
=AVERAGEIF(\$B\$2:\$B\$10000,B3,A\$2:A\$10000)
copy down.

3. Re: VBA code to Find Average

Why are you repeating the averages so many times. You should only report them once for each CNT?

4. Re: VBA code to Find Average

I tried it, but did not worked as expacted.

Here is VBA code I tried:

Sub test123()

Dim LastRow As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("E1").Select
Range("E2") = "=AVERAGEIF(\$B\$2:\$B\$10000,B3,A\$2:A\$10000)": Range("E2:E" & LastRow).FillDown

End Sub

Also please find attached excel for output.

5. Re: VBA code to Find Average

Originally Posted by protonLeah
Why are you repeating the averages so many times. You should only report them once for each CNT?

Actually I need to add one more column, having % of "Vol1" value based on given average value for each row.

4,53,600 1 1,08,800 400%
89,200 1 1,08,800 90%
72,400 1 1,08,800 70%

SO, it will be easier to compare, if I have average value displayed in each row.

I am not expert in excel. Please let me know if there is any other way to do same.

6. Re: VBA code to Find Average

Oops, a typo B3 should be B2
Originally Posted by jindon
=AVERAGEIF(\$B\$2:\$B\$10000,B2,A\$2:A\$10000)
copy down.

7. Re: VBA code to Find Average

Originally Posted by jindon
Oops, a typo B3 should be B2
Thanks a lot jindon. It worked perfectly.

Just want to know one more info. Instead of giving static value for range End, i.e. "\$B\$10000" or "A\$10000", is there any way to provide range upto last row like "B2:B" or "A2:A" as I have given here "Range("E2:E" & LastRow)".

You mean?

9. Re: VBA code to Find Average

Originally Posted by jindon
You mean?

Great jindon. Worked perfectly. Thanks again.

10. Re: VBA code to Find Average

There are currently 1 users browsing this thread. (0 members and 1 guests)