Averaging VLOOKUPs dynamically.

Good Afternoon everyone!

So, I have a spread sheet that has hundreds of rows of data arranged like they are in the example book. As you can see some have one piece of data, and others as many as four. As an output in column K, i can do a vlookup on B4, and then divide C4 by that number to get me a days on hand of the part in column B. It works perfectly every time. The problem is, if there are more than one set of data there i need to average them and i cant get that to happen dynamically. If i drag the formula down wards i will get the N/A error because if im averaging based on having 4 sets of data, then if that formula is applied to say, row 4 then E4, G4, and H4 have no data. does that make sense? if there's only one set of data its no problem. in fact its no problem for any of them if i want to manually change the formula for every row based on the number of inputs. The problem with that is there are about 531 rows with different amounts of data and also it will change every month or so....i'm at a total loss here haha. Im not sure if i explained that well enough either so please let me know. I need C4/V4 (which is an output from a vlookup). for row 5 i would need ((C5/B5)+(E5/D5))/2 to get the average and so one for 3 and four sets. I've ever tried stringing IFEERROR and ifs together and it get so complicated so fast i cant even remember what I'm doing any more!

Thank you all in advance for bearing with that entire paragraph, and any help you can give me

Codemancode

Add the VLOOKUP table to your file so it is clear how this works.

Or try

=SUM((C5/VLOOKUPB4)+(E5/VLOOKUPD4)+(G5/VLOOKUPF4)+(I5/VLOOKUPH4))/(COUNTIF(B5:I5,"<>")/2)

The VLOOKUP is for average daily use. Basically it will return the average daily use of Apples in B4, and then C4, which is the number of apples in inventory will be divided for that so i have an idea of my days on hand of apples. The VLOOKUP table is about is like 20,000 rows, but i can include it if youd like...haha. Bascially my formula right now is SUM(P5/VLOOKUP($O5,ADU!A:D,4,FALSE)). "ADU" is the name of the tab that contains the ODBC server query data.

A small sample of your VLOOKUP (10 or less rows) so we can test out solutions against your posted file..

Thank you.

s the ADU tab added to the sheet

I assume "Apple" etc are replaced by "Part" from column A (of ADU) and VLOOKUP is returning column D (many of which are zero) so we have cater for #DIV/0 and VLOOKUP when there is no "part" in B,D,F or H
?????
Adding a real example would (will) help!

Yes sir, sorry about that. apple would be a part number from A and it returns column D. DIV/0 isnt really a problem typically because most parts I forecast have a value other than 0 in column D. You see zero in the example tab because it lists EVERY part in our system automatically, even obsolete ones we dont use any more.

Try

=(SUM(C7/VLOOKUP($B7,ADU!$A$2:$D$2000,4,0)+IFERROR($E7/VLOOKUP($D7,ADU!$A$2:$D$2000,4,0),0)+IFERROR($G7/VLOOKUP($F7,ADU!$A$2:$D$2000,4,0),0)+IFERROR($I7/VLOOKUP($H7,ADU!$A$2:$D$2000,4,0),0)))/(COUNTIF(B7:I7,"<>")/2)

Apologies for the late response. That worked very well thanks! I have definitely learned a lot from asking stupid questions here lol.

Glad to helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.