Hi all. I have an internal survey that each department uses differently and this means of some 365 questions, irrelevant ones per department are deleted from the list and this messes up the lookup function. I have to make the formulas dynamic by counting what is left and identifying the category so I can calculate the score.
Each question has 5 answers. If there are 10 questions for one category that means the maximum score possible is 50. If the answers to that category equals 24 I can calculate the score as =(24/50) or 48% for the category.
If two questions (two rows) are removed, I need to be able to read the remaining category questions and sum the totals automatically, now with a total possible score of 40.
I was using VLOOKUP as I am not very good with index and match functions though this has fallen apart and gets very messy over 3 lookup functions in one cell. I would value any input as the solution will save me building a separate survey for each department. This way whatever a department leaves in, we can get a score dynamically weighted.
Bookmarks