# Average of a formula based on few conditions

• ### 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 Friday, July 14, 2017 6:32 PM Clarification
Friday, July 14, 2017 1:52 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
• 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 Wednesday, July 26, 2017 4:38 AM
• Marked as answer by 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 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%

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

 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 Wednesday, July 26, 2017 4:38 AM
• Marked as answer by Wednesday, November 22, 2017 3:32 PM
Tuesday, July 25, 2017 7:52 PM
• Hello Ram,

>>Thanks and the above formula works.

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