Dear All,
First of all I am excited to have found this forum. I am working in Oil and Gas organisation in UAE and pass most of my times on excel, trying to figure out how to resolve most calculations by myself.
Lately I'm really stuck as I have done previous audit/scoring systems but with same weight distribution (N/A, 0, 1, 2) . This case is a bit different and would gladly appreciate your help!
I'm creating a scoring method for an audit tool. The problem I am facing is that each question has a different weight (%) and the total of the questions should always equal 100% even if only one is answered. Ill try to give an easy example.
1. What type of items are in the fridge?
1a. Fruit (20%)
2a. Cheese (30%)
3a. Meat (40%)
4a. Vegetables (10%)
The Auditor will give a rating from a drop down menu that goes from N/A,0,5,10,15,20,25 etc to 100.
My problem is in the compiling of the total. If one item is N/A than that weight has to be transferred proportionally to the remaining items.
To make it clear, if 4a. is NA than that 10% needs to be redistributed to 1a, 2a and 3a in the proportion of 2.22%, 3.33% and 4.44%
Equally if 2a and 3a are NA then 70% needs to be distributed to 1a in 23.3% (so total weight for 1a would be 33.3%) and 2a in 46.6% (So total weight would be 66.6%)
I know the total would then equal to 99.9% but that's something I will tackle later (adding 0.1% somewhere i guess).
Any help would be gladly appreciated!
P.S. ill try to attach and excel in my next post.
Bookmarks