## Question:

I have a spreadsheet with 1,000 rows. Each row has six column categories with different weights (30%, 20%, 20%, 10%, 15%, 5%) that are scored either 1,2,3,4,5 or N/A and then a rating which compiles the overall score, so the first row is 5*.3, 2*.2, 1*.1, 1*.15, 5*.05 for a total of 2.4. I want to reassign the values if a column has N/A so if the first column which is worth 30% has an N/A, I want the new remaining five values to be worth 26%, 26%, 16%, 21%, 11% (yes I would like to add 6% to each and not redistribute the 30% based on current weights). How do I do this with VBA code? If two columns have N/A then I will distribute the total weight of those two columns to the other four and so on. I’ve gotten the suggestion here of not doing the 60 if-thens for all of the combos and so I tried to handle it by adding the NA percentages, counting the NAs, then adding the new weights to the old weights. I know the last formula itself needs to be slightly edited, but I would appreciate help with why CatPercentage1 is coming up 0 even though AD2 is N/A and as importantly how to make this dynamic to account for all the rows. Thanks!## Answer:

I hope this helps to explain and also match with your desired outcome:

- SUMIF to calculate the percentages of columns with N/A
- COUNTIF to count columns with a value <> N/A
- add the share to all 6 “base” percentages (total is more than 100% but does not matter as N/A columns will be multiplied with 0)
- multiple with the respective ”NEW” percentages related factor replacing N/A with 0; adding all together

Edit: the 8% share in the second line is just due to the display format without decimal and can be change e.g. to 2 decimal

If you have better answer, please add a comment about this, thank you!