+ Reply to Thread
Results 1 to 11 of 11

Help please: Formula for Averaging Line Items

  1. #1
    Registered User
    Join Date
    06-06-2016
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question Help please: Formula for Averaging Line Items

    Hi all, I have done some searching and cannot come up with what I need - I would really appreciate your assistance!

    I use software at work that automatically generates a monthly COGS report to show our supply usage for each month. I need to find the average item count per location, as illustrated in the attached sample using fruit. I'm looking for a single formula to use in the F column to get the average item per location. I'm sure you can do this with VBA, but I want to avoid VBA if at all possible so other users can understand how this sheet works.

    Thanks for your help!

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Help please: Formula for Averaging Line Items

    Hi and welcome.

    put this in F2 and drag down

    =IF(A2=A1,"",SUMIF($A$2:$A$7,A2,$C$2:$C$7)/D2)
    Happy with my advice? Click on the * reputation button below

  3. #3
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: Help please: Formula for Averaging Line Items

    Crooza's answer works until you get to the last row, when you get an average of 5.4 not 13.5 which is what I think it should be if I've understood the question

    I did it as a pivot table, but I wasn't sure if you were trying to get averages by fruit or sales order number?
    Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.

  4. #4
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: Help please: Formula for Averaging Line Items

    Scrub that... I think I misunderstood the question

  5. #5
    Registered User
    Join Date
    06-06-2016
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help please: Formula for Averaging Line Items

    Quote Originally Posted by shirleyxls View Post
    Crooza's answer works until you get to the last row, when you get an average of 5.4 not 13.5 which is what I think it should be if I've understood the question

    I did it as a pivot table, but I wasn't sure if you were trying to get averages by fruit or sales order number?
    Hey shirleyxls, I'm trying to get the average per sales order. I just tried Crooza's answer and it gives me 5.4 on the last row. I have no experience with pivot tables, so please elaborate

  6. #6
    Registered User
    Join Date
    06-06-2016
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help please: Formula for Averaging Line Items

    Hi Crooza!

    Such a simple answer, I like it! Thank you. I should be able to work this into the spreadsheet I use easily.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help please: Formula for Averaging Line Items

    Perhaps in F2 copied down.

    =IFERROR(AVERAGEIFS($C$2:$C$7,$D$2:$D$7,E2),"")

    But wouldn't a Pivot Table be even better since it doesn;t involve any formulae. See attached
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Registered User
    Join Date
    06-06-2016
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help please: Formula for Averaging Line Items

    Quote Originally Posted by Richard Buttrey View Post
    Perhaps in F2 copied down.

    =IFERROR(AVERAGEIFS($C$2:$C$7,$D$2:$D$7,E2),"")

    But wouldn't a Pivot Table be even better since it doesn;t involve any formulae. See attached
    Nice pivot table - that might be useful. I'd prefer a formula because it's easier for my other users to understand, and all of the data needs to be visible on one spreadsheet table for upper management.

  9. #9
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: Help please: Formula for Averaging Line Items

    Ok, so here's how I did it with a pivot table:

    Select Cell A1, then go to Insert > Pivot table. This should automatically select all the rows and columns. In the dialog, select to put the pivot on a new worksheet or an existing worksheet - I usually like to keep pivot tables on a separate sheet. Click OK.

    You will now see a panel on the right showing Pivot Table fields with four areas at the bottom. Drag Sales Order field in the Rows box, drag the Locations field to the Columns box, and the Qty to the Values box. Click on the drop down arrow by the Qty field that you just put into the Values box, and choose Value Field settings. In the dialog box, choose Average in the Summarize value field by area. I also clicked on the Number format and changed this to 2 decimal places.

    I've attached a copy of the file I made with both Crooza's answer and the pivot table so you can see both.

  10. #10
    Registered User
    Join Date
    06-06-2016
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help please: Formula for Averaging Line Items

    Quote Originally Posted by shirleyxls View Post
    Ok, so here's how I did it with a pivot table:

    Select Cell A1, then go to Insert > Pivot table. This should automatically select all the rows and columns. In the dialog, select to put the pivot on a new worksheet or an existing worksheet - I usually like to keep pivot tables on a separate sheet. Click OK.

    ...

    I've attached a copy of the file I made with both Crooza's answer and the pivot table so you can see both.
    Thanks for the how-to! That's an interesting way to do it, I will have to play with pivot tables more.

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help please: Formula for Averaging Line Items

    Quote Originally Posted by xFsshhh View Post
    Nice pivot table - that might be useful. I'd prefer a formula because it's easier for my other users to understand, and all of the data needs to be visible on one spreadsheet table for upper management.
    The Pivot Table doesn't have to be on a sheet of its own. It can be alongside the data if you prefer.

+ 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. Sumifs when there are several line items
    By Alex.Venables20 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2015, 06:00 AM
  2. How do I simplify this line graph -- over 200 line items??
    By klove in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-17-2015, 08:51 AM
  3. Referencing line items from another tab
    By amartino44 in forum Excel General
    Replies: 3
    Last Post: 10-10-2013, 03:05 PM
  4. Combining multiple line items into one line item based on column.
    By mguz018 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-20-2012, 07:22 PM
  5. Separate Line items
    By seif10mes in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2011, 10:08 AM
  6. New Line Items at Runtime
    By GHopson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2005, 02:15 PM
  7. Replies: 0
    Last Post: 08-29-2005, 07:40 AM

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