Help creating an formula to multiply weights to proportional scale to create an index score.
I have narrowed my use case to simplify the question but I will need to scale this once I can get it working.
Sheet Columns
For this example I have 8 columns. In one I have a dropdown of options listed in sequence as 1. 2. 3. 4. and 5 (lets call this column A). In another column I have a weight I've assigned (Lets call this column B). In 5 other columns, I have a proportional multiplier that would apply depending on the selected dropdown (lets call these columns vz). I have another column to house this formula (lets call this column C)
Workflow
A person will make a selection form the dropdown in column A. Column C would then take column B and multiply it by the appropriate proportional multiplier. If Option 1. is selected column C would multiply column B and column V. If 2. was selected, column C would multiply column B and column W and so on and so forth.
Issue
I have tried various if and contains to help C identify the selected option in A an multiply it by a column in VZ. The formulas I've tried to no avail didn't look at other options as I was intentional about let me see if it can identify one option and simply multiplying it by a 3. If I can do that, then I'd try replacing that 3 with the proper column. If that worked I'd try scaling it to include the other moving parts but I can't even get past the first part. Help is needed Thank you.
Answers

Are you able to provide some screenshots with mock data manually entered for context?

@Paul Newcome does this help?

Assuming your dropdown selections in the [Certainty of Schedule] column will always start with the appropriate number followed by a period, you would use something along the lines of the below (updating column names to match what you have in your sheet.
=[Certainty of Schedule Weight]@row * INDEX([1st Weight Column]@row:[Last Weight Column]@row, VALUE(LEFT([Certainty of Schedule]@row, FIND(".", [Certainty of Schedule]@row)  1)))

I attempted this solution and cannot get it to work. Through different iterations, I keep getting either #UNPARSEABLE or #INVALID DATA TYPE.

Can you show your most recent attempt open in the sheet as if you are about to edit it?
Help Article Resources
Categories
Check out the Formula Handbook template!