+ Reply to Thread
Results 1 to 5 of 5

Finding average amount of days

  1. #1
    Registered User
    Join Date
    08-05-2017
    Location
    AUSTRALIA
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    23

    Finding average amount of days

    Hi,

    I have the attached workbook with 2x worksheets.
    'Lead Times' sheet has each time a part was ordered for our inventory. Inculdes order date, part#, arrival date, total days.
    This sheet displays the same part number multiple times to represent each time it was ordered.

    'Average Lead Time' sheet shows my inventory with each part number only being displayed once.

    I'm plesae requiring (preferrably V lookup if possible) a formula in column B of work sheet 'Average Lead Time' to find the number of orders via the part number in 'Lead Times' sheet, add then find the average amount of days for that part number to arrive.

    Thanks very much in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Finding average amount of days

    Average:
    =IFERROR(AVERAGEIF('Lead Times'!B:B,'Average Lead Times'!A2,'Lead Times'!D:D),"")

    Count:
    =COUNTIF('Lead Times'!B:B,A2)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-05-2017
    Location
    AUSTRALIA
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    23

    Re: Finding average amount of days

    That's perfect!
    Thank you very much Glenn Kennedy

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Finding average amount of days

    You're welcome...

  5. #5
    Registered User
    Join Date
    03-10-2010
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    68

    Re: Finding average amount of days

    Check the attached sheet if this is what you are looking for.
    Below formula is giving average number of days to order the respective part.

    =IFERROR((SUMIF('Lead Times'!B:D,'Average Lead Times'!A2,'Lead Times'!D:D)/COUNTIF('Lead Times'!B:B,'Average Lead Times'!A2)),0)
    Attached Files Attached Files

+ 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. Replies: 2
    Last Post: 04-17-2018, 09:45 PM
  2. [SOLVED] Average and Standard over the last 344 days excluding the previous 7 days
    By Renegade115 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-01-2018, 09:16 PM
  3. Replies: 13
    Last Post: 09-22-2016, 09:18 AM
  4. [SOLVED] calculate amount of days by name
    By RachelMads02 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-05-2016, 03:02 AM
  5. Replies: 4
    Last Post: 06-22-2016, 11:03 PM
  6. Replies: 1
    Last Post: 08-25-2010, 11:25 AM
  7. finding last and creating average amount income to date
    By Jerry Kinder in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-04-2006, 01:10 PM

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