Weighted score - redistributing when 1 category is N/A

[cacos]

Hi community, hoping you can help me with the following: I have a form where different aspects of a student are rated, each having different weights.

Form is composed of 2 different sections, and each category is rated from 0 to 3. Depending on the weight for each category (they vary), student gets a total score.

I need to include an N/A option for all categories, where if selected, that category is ignored and the total score for that section is redistributed among the remaining categories (e.g. out of 50 total points, if one category in Section 1 is N/A, those 50 points should be distributed proportionally among the remaining categories, each keeping their same proportion as before).

I'm attaching a sample file, hopefully that'll make it easier.

Thank you!

 

[Shteven]

Is it necessary that Section 1 and Section 2 remain split 50/50 during the redistribution?

 

[cacos]

Hi! Right yes, they need to stay 50 each. And the other categories should be distributed proportionally when 1 of them is N/A.

 

[Chihiro]

Add column for "Adjusted".


Formula for "Adjusted" would be.
=IF(G4="N/A","",D4/SUMPRODUCT(--($G$4:$G$8<>"N/A"),$D$4:$D$8)*50)

Change "% Score" formula to.
=IFERROR((INDEX($K$4:$K$7,MATCH(G4,$J$4:$J$7,0))*E4)*0.01,"")

See attached as well.

 

[cacos]

This is great, really smooth.

Appreciate your help very much Chihiro. Have a great weekend!