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 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.
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!