Help creating an formula to multiply weights to proportional scale to create an index score.

dmsbhs

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 v-z). 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 V-Z. 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.

Paul Newcome

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

dmsbhs

@Paul Newcome does this help?

Paul Newcome

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)))

dmsbhs

@Paul Newcome

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

Paul Newcome

Can you show your most recent attempt open in the sheet as if you are about to edit it?