Help understanding report based on VLOOKUP and SUMPRODUCT formulas

[codonnell73]

I received such great help with my first request I couldn’t wait to try again. I have been brooding over this old report I generated years ago with a former VP. How it worked was explained to me but years later I have forgotten and I’m trying to recreate something similar. I’ve attached the workbook and would appreciate answers to the following:

(1) I have applied a weighting factor (column E) to 12 different series of data (column C). Note the SUMPRODUCT function in row 24 that is intended to sum all values and produce an aggregate number that takes into account the relative weighting of the numbers. I don’t know much about this function and borrowed it from yet another report.

QUESTION(S): I’m unsure if I’ve used the function correctly and if the results in row 24 make sense? Can someone help with a plain English explanation of the expression?

(2) Note the picklist @ B30 that is based off a series of values starting @ C46. The pick list feeds a VLOOKUP @F45 and then the related series of data populates a row of data at F47 that is used to generate the line chart above.

QUESTION(S): How does the VLOOKUP @F45 work? I know the first reference is the pick list itself (B30). The second argument (C46:D57) refers to the range of the pick list and an associated numeric value - what is the purpose of the numeric value and why are they out of sequence? When I change the value in D50 to a ‘5’ to fix the sequence the chart will break. What does the remainder of the argument refer to?

I can see the dependencies and don’t fully understand how the pick list is associated the series of data above etc. I would appreciate a plain english description of this workbook from which i can make edits. I ultimately want to add or remove items and be able to appropriately edit the ranges.

Thanks in advance!

Christopher

[humdingaling]

SUMPRODUCT works by taking the product(multiply) every row in the array
put simply in below example
1 1
2 2
3 3

1x1 + 2x2 + 3x3 = 14
more reading here
http://office.microsoft.com/en-au/ex...005209293.aspx

QUESTION(S): How does the VLOOKUP @F45 work? I know the first reference is the pick list itself (B30). The second argument (C46:D57) refers to the range of the pick list and an associated numeric value -

the vlookup is to take the rows to offset in formula F47 to O47
the offset works by starting at row 4

Please Login or Register  to view this content.

start at f4, f45 then tells it how many rows to go down in your sheet...it starts 11
so it goes 11 rows down to reach F15

what is the purpose of the numeric value and why are they out of sequence? When I change the value in D50 to a ‘5’ to fix the sequence the chart will break. What does the remainder of the argument refer to?


its out of sequence to cater for the blank rows which is your theme
reason for that is explained above with offset

[codonnell73]

SUMPRODUCT works by taking the product(multiply) every row in the array
put simply in below example
1 1
2 2
3 3

1x1 + 2x2 + 3x3 = 14
more reading here
http://office.microsoft.com/en-au/ex...005209293.aspx

[

Thanks for the quick response -I'm working through your feedback now. One quick clarification on my question on SUMPRODUCT. Other uses of this online to calculate a weighted average used expressions such as =SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9). Note I am not dividing the SUM into the SUMPRODUCT but the result still seems correct? Is it because Im using percentiles? Also I'm unclear if my expression needs to be in parentheses?

Thanks again. This forum is awesome and I wish I could at least pop by and clean your gutters in return!

Christopher

[humdingaling]

hahahah no need for that

=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
You are dividing the sumproduct by the sum

this is basically doing
(B2*C2 + B3 *C3 + etc )/ sum (C2:C9)

cannot comment if it is correct...it is your way of working
the formula working as intended though