Reference a Page to calculate weight(Formula)

skdimitri

Hello Community,

I am trying to reference a page which has certain weights assigned to the categories which the users will select while filling out the form. Based on the selection of the categories the weight is calculated and the total weight is shown. I am attaching the Screenshot of the page which has the categories and their corresponding weights and the other sheet which has the formula to calculate the total weight.

Formula to Calculate Weight :

=SUM(VLOOKUP([Stakeholder level of interest]@row:[Commitment of Stakeholder]@row))

Error :

Incorrect Argument Set

Screenshot Categories 1 shows the categories selected by the users while filling out the form and Screenshot Weight shows the corresponding Weight Value for each category.

weights.PNG

Categories 1.PNG

Kyle Walker

Your vlookup isn't complete. It needs a range of columns to look at, a value to look for, a column to get a value from, and true or false. It still won't do what you are wanting though as it will pull only one value to your sum formula.

Could you give more context as to what you are trying to do?

skdimitri

Hi kyle

I am attaching 2 files out of which one file has the categories with their corresponding weights(VLookup-RAC Initiative-Engagement Strategy) and the 2nd file has the formula for weights(Project Stakeholder Management Tool)

Based on the selection for each category like Stakeholder level of interest,Stakeholder Influence on Project,Level of Impact on the Stakeholder and Commitment of Stakeholder the Weight(C) is calculated. When the user makes this category selection a corresponding weight is calculated and the formula for the weight of each categories is as follows:

Interest(W)(Based on Stakeholder level of interest) : =VLOOKUP([Stakeholder level of interest]@row, {VLookup-RAC Initiative-Engagement Strategy Range 1}, 2, false)

Influence(W)(Based on Stakeholder Influence on Project) : =VLOOKUP([Stakeholder Influence on Project]@row, {VLookup-RAC Initiative-Engagement Strategy Range 1}, 2, false)

Impact(W)(Based on Level of Impact on the Stakeholder) : =VLOOKUP([Level of Impact on the Stakeholder]@row, {VLookup-RAC Initiative-Engagement Strategy Range 1}, 2, false)

Commitment(W)(Based on Commitment of Stakeholder) : =VLOOKUP([Commitment of Stakeholder]@row, {VLookup-RAC Initiative-Engagement Strategy Range 1}, 2, false)

P.S : These formulas need modification as they are throwing errors but just to give you an idea how the sheet is working.

Based on the Weight, I also want to calculate the Engagement Strategy for this Stakeholder.

Current Formula:

Weight(C) : =SUM(VLOOKUP([Stakeholder level of interest]@row:[Commitment of Stakeholder]@row))

Engagement Strategy for this Stakeholder : =IF([Weight(C)]@row >= 15, "Collaborate (Key Players) - Requirement: strong buy-in", IF([Weight(C)]@row >= 11, "Active Consultation to maintain confidence", IF([Weight(C)]@row >= 9, "Maintain Interest", IF([Weight(C)]@row >= 6, "Keep Informed - Monitor and Respond", IF([Weight(C)]@row >= 3, "Value and Respect Peoples and Cultures")))))

Please feel free to drop me a message if you have further questions.

Regards.

VLookup-RAC Initiative-Engagement Strategy.xlsx

Project Stakeholder Management Tool.xlsx

Eric Law

@skdimitri I think it would help you to have a few helper columns for each category that puts the weight there.

=Vlookup([Commitment of Stakeholder]@row, {VLookup-RAC Initiative-Engagement Strategy Range 1}, 2, false) for each one and then just total the weights on the sheet itself with =Sum(Weight1@row:WeightX@row)

skdimitri

@Eric Law Can you elaborate this as the formula for the Vlookup is not working for me and where can I add the =Sum(Weight1@row:WeightX@row) in the sheet.

For your reference I have added the actual sheets with all the columns in it so can you please show how can I do it that would be very helpful.

Regards,

Shubham Kolekar.

Eric Law

@skdimitri

If your sheets are like the files you have here.

For Stakeholder Weight, your formula should be =vlookup([Stakeholder level of interest)@row, {VLookup-RAC Initiative-Engagement Strategy Range 1}, 3, false)

Do that for all 4 sections with the proper lookup

Create a TOTAL WEIGHT column and sum the 4 weights. Should just be =SUM([Stakeholder level of interest]@row:[Commitment of Stakeholder]@row)