locked
Average of a formula based on few conditions RRS feed

  • Question

  • Hello Forum,

    I am working on a 40MB excel file with big tables and formulas. The file has details of different products and their respective supplier names along with their processing time - start and end time since Yr. 2012 (daily). I am looking for an option where I want to do an average on the %completion on a given time. (some time in the past. example : 1/1/14) Formula : (1/1/14 – Start date)/(End date – start date). But then this is just unique to one product. How do I do an average of all the products ( % completion ) at a given time in the past ? 

    Main Data :

    Product Start Date End Date Process Time (days) Supplier
    A 1-Jan-12 4-Jan-12 3 AA
    B 2-Jan-12 8-Jan-12 6 AA
    C 3-Jan-12 6-Jan-12 3 BB
    D 4-Jan-12 10-Jan-12 6 BB
    E 5-Jan-12 8-Jan-12 3 AA
    F 6-Jan-12 10-Jan-12 4 BB

    Average %Completion

    Status Date Supplier AA Supplier BB
    1-Jan-12 0% NA
    2-Jan-12 33% NA
    3-Jan-12 41% 0%
    4-Jan-12 67% 33%
    5-Jan-12 50% 41%
    6-Jan-12 50% 67%
    7-Jan-12 75% 38%
    8-Jan-12 100% 58%
    9-Jan-12 NA 79%
    10-Jan-12 NA 100%

    Manual Formula for 7-Jan-2012 :  Supplier AA = [ NA for product A + [(7 jan 2012 - 2 Jan 2012 for product B) / 6 days] + [(7 Jan 2012 - 5 Jan 2012 for Product E) / 3 days]]

    Thanks and any help will be appreciated.

    Regards,
    Ram


    • Edited by CuriousExcelen Friday, July 14, 2017 6:32 PM Clarification
    Friday, July 14, 2017 1:52 PM

Answers

  • Array-enter this:

    That will give the average as 16.67% - (33 % + 0%)  / 2

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,($A2-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))

    But you might want a weighted average - a task that is two days long should be weighted differently in the average from one that is 100 days long: total work days completed / total work days expected  - this one will return 11%, which is a better metric.

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",SUM(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,$A2-Table1[Start Date]))))/SUM(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,Table1[End Date]-Table1[Start Date]))))))


    Friday, July 14, 2017 7:02 PM
  • Fill in C1 with X and E1 with Y, then use this in B3:

    =IFERROR(IF($A3<MIN(IF(Table1[Supplier]=B$2,Table1[Start Date])),"NA",IF($A3>MAX(IF(Table1[Supplier]=B$2,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$2,IF(Table1[Category]=B$1,IF($A3<Table1[Start Date],"",IF($A3>Table1[End Date],"",($A3-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))),"NA")

    • Proposed as answer by Chenchen Li Wednesday, July 26, 2017 4:38 AM
    • Marked as answer by CuriousExcelen Wednesday, November 22, 2017 3:32 PM
    Tuesday, July 25, 2017 7:52 PM

All replies

  • Array-enter this formula (entered using Ctrl-Shift-Enter) into cell B2, with the dates starting in A2 and extending down A, and AA in cell B1 and BB in cell C1 (I have assumed you are using Tables and the table is named Table1)

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],0,IF($A2>Table1[End Date],1,($A2-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))

    Then copy the formula down and across.

    In your example table, supplier AA on Jan 2 should be 11% - not 33%.  If you don't agree, then you need to better explain your logic.



    Friday, July 14, 2017 4:41 PM
  • Hi Bernie,

    Thanks for spending time on my request. I have to check the formula if it works, but i would like to let you know that the manual formula for supplier AA on Jan 2 is below :

    Manual Formula for 2-Jan-2012 :  Supplier AA = [[(2 jan 2012 - 1 Jan 2012 for product A) / 3 days] + [(2 Jan 2012 - 2 Jan 2012 for Product B) / 6 days] + NA for Product E].

    = 33%+0% = 33%

    I am guessing that your formula takes all the empty cells or zero cells into consideration as well. 



    • Edited by CuriousExcelen Friday, July 14, 2017 6:31 PM Clarification
    Friday, July 14, 2017 6:21 PM
  • Array-enter this:

    That will give the average as 16.67% - (33 % + 0%)  / 2

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,($A2-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))

    But you might want a weighted average - a task that is two days long should be weighted differently in the average from one that is 100 days long: total work days completed / total work days expected  - this one will return 11%, which is a better metric.

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",SUM(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,$A2-Table1[Start Date]))))/SUM(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,Table1[End Date]-Table1[Start Date]))))))


    Friday, July 14, 2017 7:02 PM
  • Hello Ram,

    You could use the following UDF. Use formula =Supplier(A2,"AA") to calculate Supplier AA and =Supplier(A2,"BB") to calculate Supplier BB.

    Function Supplier(rng As Date, sup As String) As Variant Dim tb As ListObject Set tb = Sheet1.ListObjects("Table1") Dim aryStart() As Date Dim aryEnd() As Date i = 1 For Each cell In tb.ListColumns("Supplier").DataBodyRange If cell.Value = sup Then ReDim Preserve aryStart(i) aryStart(i) = cell.Offset(0, -3) ReDim Preserve aryEnd(i) aryEnd(i) = cell.Offset(0, -2) i = i + 1 End If Next Dim j As Integer, val As Double j = 0 val = 0 For i = 1 To UBound(aryStart())

    If rng >= aryStart(i) And rng <= aryEnd(i) Then j = j + 1 val = val + (rng - aryStart(i)) / (aryEnd(i) - aryStart(i)) End If Next If j = 0 Then Supplier = "NA" Else Supplier = val / j End If End Function

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 19, 2017 7:45 AM
  • Hi Bernie,

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,($A2-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))

    gives me an error #VALUE! . I am not sure if I am missing any brackets or anything of that sort. When I tried to understand the formula, I noticed that the AVERAGE, MAX and MIN functions have only (Number 1) and not (Number 2, 3, ...) It may not be satisfying the format requirements.

    I am trying to understand the logic behind the formulas so that I can tweak or correct the formulas, more specifically the min, max, average and if loops.

    Thanks once again for your time.

    Regards,

    Ram 

    Friday, July 21, 2017 6:05 PM
  • First - make sure that you have entered the formula using Ctrl-Shift-Enter.

    Otherwise, I will have to wait until Monday to reply - my test workbook is on another machine and it will be easier if I wait until then.

    Bernie


    Friday, July 21, 2017 6:52 PM
  • Excel 2016 Pro Plus with "Get & Transform"
    No formulas, no VBA macro.
    http://www.mediafire.com/file/9gt38vmwujx86w9/07_22_17b.xlsx


    Sunday, July 23, 2017 11:11 PM
  • Hi Bernie,

    Thanks for looking in to my request. I tried the below formula again and found that i get different numbers for 7-Jan-12 and 6-Jan-12. Red font is the array formula that you helped out with.. And black is manual formula which is the best answer for my problem.

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],1,($A2-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))

    Dates AA BB AA (Array) BB(Array)
    1-Jan-12 0% NA 0% NA
    2-Jan-12 17% NA 17% NA
    3-Jan-12 42% 0% 42% 0%
    4-Jan-12 67% 17% 67% 17%
    5-Jan-12 50% 42% 50% 42%
    6-Jan-12 50% 44% 67% 44%
    7-Jan-12 75% 38% 83% 58%
    8-Jan-12 100% 58% 100% 72%
    9-Jan-12 NA 79% NA 86%
    10-Jan-12 NA 100% NA 100%


    Thanks again for your time.

    Regards,

    Ram

    AA2

    Tuesday, July 25, 2017 3:17 PM
  • This fixes all but Jan 5, which for AA returns 25% instead of 50%:

    (all I changed was the number 1 to "" in this part 

    IF($A2<Table1[Start Date],"",

    Here's the whole formula:

    =IF($A2<MIN(IF(Table1[Supplier]=B$1,Table1[Start Date])),"NA",IF($A2>MAX(IF(Table1[Supplier]=B$1,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$1,IF($A2<Table1[Start Date],"",IF($A2>Table1[End Date],"",($A2-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))

    • Proposed as answer by Chenchen Li Wednesday, July 26, 2017 4:38 AM
    Tuesday, July 25, 2017 5:13 PM
  • Thanks and the above formula works. can I request to understand this formula as I have some more conditions to this resultant table. Like, My table 1 has two more columns with sub categories (X, Y) to product (A, B, C, D, E....)

    Example of table 1 :

    Product Category Start Date End Date Process Time (days) Supplier
    A X 1-Jan-12 4-Jan-12 3 AA
    B Y 2-Jan-12 8-Jan-12 6 AA
    C X 3-Jan-12 6-Jan-12 3 BB
    D Y 4-Jan-12 10-Jan-12 6 BB
    E X 5-Jan-12 8-Jan-12 3 AA
    F Y 6-Jan-12 10-Jan-12 4 BB

    Manual Formula Answers

    X Y
    Dates AA BB AA BB
    1-Jan-12 0% NA NA NA
    2-Jan-12 33% NA 0% NA
    3-Jan-12 67% 0% 17% NA
    4-Jan-12 100% 33% 33% 0%
    5-Jan-12 0% 66% 50% 17%
    6-Jan-12 33% 100% 67% 17%
    7-Jan-12 67% NA 83% 38%
    8-Jan-12 100% NA 100% 58%
    9-Jan-12 NA NA NA 79%
    10-Jan-12 NA NA NA 100%

    Thanks

    Tuesday, July 25, 2017 7:34 PM
  • Thanks for the prompt reply. I have never used UDF and have always managed my way through Formulas in excel. How do I implement this solution in my excel work book.

    Regards,

    Ram

    Tuesday, July 25, 2017 7:36 PM
  • Fill in C1 with X and E1 with Y, then use this in B3:

    =IFERROR(IF($A3<MIN(IF(Table1[Supplier]=B$2,Table1[Start Date])),"NA",IF($A3>MAX(IF(Table1[Supplier]=B$2,Table1[End Date])),"NA",AVERAGE(IF(Table1[Supplier]=B$2,IF(Table1[Category]=B$1,IF($A3<Table1[Start Date],"",IF($A3>Table1[End Date],"",($A3-Table1[Start Date])/(Table1[End Date]-Table1[Start Date])))))))),"NA")

    • Proposed as answer by Chenchen Li Wednesday, July 26, 2017 4:38 AM
    • Marked as answer by CuriousExcelen Wednesday, November 22, 2017 3:32 PM
    Tuesday, July 25, 2017 7:52 PM
  • Hello Ram,

    >>Thanks and the above formula works.

    I suggest you mark helpful post as answer if your issue has been resolved. For a new requirement, please post a new thread.

    >>How do I implement this solution in my excel work book.

    To work with UDF, please visit Create Custom Functions in Excel 2007 for detail information.

    Here is the modified UDF for your original requirement. Please go to VBA Editor (ALT+F11) and create a new module, then paste the code. Go back the sheet, enter formula like =Supplier(A11,"AA") or =Supplier(A11,"BB")

    Note, the workbook should be saved into Macro-enabled workbook if you include UDF.

    Function Supplier(rng As Date, sup As String) As Variant
    Dim tb As ListObject
    Set tb = Sheet1.ListObjects("Table1")
    Dim aryStart() As Date
    Dim aryEnd() As Date
    i = 1
    For Each cell In tb.ListColumns("Supplier").DataBodyRange
    If cell.Value = sup Then
    
    ReDim Preserve aryStart(i)
    aryStart(i) = cell.Offset(0, -3)
    
    ReDim Preserve aryEnd(i)
    aryEnd(i) = cell.Offset(0, -2)
    i = i + 1
    End If
    Next
    
    Dim j As Integer, val As Double
    j = 0
    val = 0
    For i = 1 To UBound(aryStart())
    
    If rng >= aryStart(i) And rng <= aryEnd(i) And rng - aryStart(i) <> 0 Then
    j = j + 1
    val = val + (rng - aryStart(i)) / (aryEnd(i) - aryStart(i))
    End If
    Next
    If j = 0 Then
    Supplier = "NA"
    Else
    Supplier = val / j
    End If
    End Function
    

    Result:

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 26, 2017 4:47 AM
  • Thanks Bernie. I used your idea in a different way and glad that it helped with what I wanted to calculate.

    Wednesday, November 22, 2017 3:33 PM
  • thanks for your help.
    Wednesday, November 22, 2017 3:33 PM