Calculating Weighted Average

Question

I'm trying to work on a sheet and need to calculate the weighted average based on an item code and it's corresponding weight and price, if any. The problem I'm running into is that I can't get my formula to ignore blank cells. I thought I was close on it but I still can't get it. Here's my formula:

=SUMPRODUCT(--(E4:E407=L5),J4:J407,G4:G407)/SUMIF(J4:J407,">0",G4:G407)

E- is my item codes where it needs to equal L- which is one code J- is my price where a lot of cells don't have a value in them G- is the weight where I don't need it to read the weight if I don't have values in J

Can anyone help me with this?

Answer 1

I would look into using Excel's ISNUMBER function. It will return true if there is a value in the cell. See this helpful tutorial on how to use it:

http://www.exceltip.com/excel-array-formulas/ignoring-blank-cells-when-performing-calculations.html

If you'd like to know how to use it in your specific example it'd be helpful to see some sample data.