Average Lead Time Formula

[bobgrand]

Hello all,

I have an inventory list along with the lead time of each item. Can someone help me with a formula to get the 1 average lead time number for all items for each vendor.

VENDOR A has 330 items - Average Lead Time for Vendor A 5.65
VENDOR B has 693 items - Average Lead Time for Vendor B 3.54

Any help is greatly appreciated

Bob

 

[Dr. Demento]

If I’m understanding, you’re asking for an average of averages? In which case you want to look at geometric mean. There are online calculators or Wikipedia has a decent explanation.

 

[bobgrand]

Thanks Doc.

That was an interesting read. I used the formula in excel and it does seem to give a better number. Is there a way to subtotal the GEOMEAN when filtering down to specific vendors?

 

[Dr. Demento]

Honestly, I don’t know for sure; you’d want a function for SUBPRODUCT (similar to SUBTOTAL) but I don’t see it off hand

 

[Dr. Demento]

I did have one additional thought about your second question. Have you thought of using SUMPRODUCT to isolate results for specific vendors? No really a pro in that area, so would recommend starting a separate thread if the Google fails you.

 

[Marcelo Branco]

Just an example - to get GEOMEAN from only visible cells in say B2:B9 this array formula should work.

=GEOMEAN(IF(SUBTOTAL(3,OFFSET(B2:B9,ROW(B2:B9)-ROW(A2),0,1)),B2:B9))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Marcelo Branco]

A more robust formula - works with rows hidden manually or by filtering.

=GEOMEAN(IF(SUBTOTAL(103,OFFSET(B2:B9,ROW(B2:B9)-ROW(A2),0,1)),B2:B9))
confirmed with Ctrl+Shift+Enter, not just Enter

M.