+ Reply to Thread
Results 1 to 10 of 10

VBA code to Find Average

  1. #1
    Registered User
    Join Date
    05-31-2020
    Location
    Jaipur
    MS-Off Ver
    Office 365
    Posts
    5

    Question 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.

    Could anyone please help on this.

    Please find attached excel for sample data.
    Attached Files Attached Files
    Last edited by pankaj25; 05-31-2020 at 01:05 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,638

    Re: VBA code to Find Average

    VBA?

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

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,048

    Re: VBA code to Find Average

    Why are you repeating the averages so many times. You should only report them once for each CNT?
    Attached Files Attached Files
    Last edited by protonLeah; 05-31-2020 at 01:56 AM.
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    05-31-2020
    Location
    Jaipur
    MS-Off Ver
    Office 365
    Posts
    5

    Re: VBA code to Find Average

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-31-2020
    Location
    Jaipur
    MS-Off Ver
    Office 365
    Posts
    5

    Re: VBA code to Find Average

    Quote Originally Posted by protonLeah View Post
    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.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,638

    Re: VBA code to Find Average

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

  7. #7
    Registered User
    Join Date
    05-31-2020
    Location
    Jaipur
    MS-Off Ver
    Office 365
    Posts
    5

    Re: VBA code to Find Average

    Quote Originally Posted by jindon View Post
    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)".

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,638

    Re: VBA code to Find Average

    You mean?
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-31-2020
    Location
    Jaipur
    MS-Off Ver
    Office 365
    Posts
    5

    Re: VBA code to Find Average

    Quote Originally Posted by jindon View Post
    You mean?
    Please Login or Register  to view this content.

    Great jindon. Worked perfectly. Thanks again.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,638

    Re: VBA code to Find Average

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find a Text, average last 20,if < 20 average all available.
    By richhhh in forum Excel General
    Replies: 42
    Last Post: 08-28-2016, 09:51 PM
  2. Find Average of different columns and colour code it
    By Johnty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2014, 04:09 AM
  3. [SOLVED] Find Average, Return Column Header of Number Closest To Average
    By djmyers in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-07-2013, 04:19 PM
  4. Index/Match to find Starting Point of Average, Ending Point of Average Variable
    By tgentry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2012, 01:04 AM
  5. Replies: 6
    Last Post: 07-09-2012, 11:06 AM
  6. Trying to find average
    By Ashleyflower in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-27-2011, 05:21 PM
  7. [SOLVED] UDF code to find specific text in cell comments, then average cell values
    By bruch04 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-05-2005, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1