VBA code to Find Average

VBA?

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

Jindon, Thanks for your code.

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.

Originally Posted by protonLeah

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

Thanks for your reply, protonLeah.

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.

Oops, a typo B3 should be B2

Originally Posted by jindon

=AVERAGEIF($B$2:$B$10000,B2,A$2:A$10000)
copy down.

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?

Please Login or Register  to view this content.

Originally Posted by jindon

You mean?

Please Login or Register  to view this content.

Great jindon. Worked perfectly. Thanks again.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.