Need help with drop down and formula

[SMAsh67]

Hi, I am a total novice and trying to figure out a bunch of stuff in excel 2016 so that I can create a form. I'm doing fairly well but now I'm stuck...

I'm trying to make multiple drop-down lists (I've made them using data validation) that have 3 options: Yes, No, N/A. I would like the 'No' option in each drop down to deduct a number value. So for instance, drop down 1 if No is selected it deducts 5 points from a cell with a value of 100%. Then, drop down 2 if No is selected it deducts 20 points from the same previously mentioned cell with the 100% value (which would now have a value of 95%) to make it now total 75%. There are several of these. I never want the total to be negative (i.e. -200) I want the cell that starts at 100%, to go no lower than 0.

Ultimately I would have a way (button?) to reset all of the drop downs to the default of 'Yes' if possible, but at the moment I'm focused on the problem I have described above.

For sake of the layout and the aesthetics, I need to use drop downs. I am not opposed to using VB or ActiveX I just don't understand them very well yet.

Any help would be very much appreciated!

 

[dado6612]

Is this what you need:
If dropdowns in A1;A2;A3
Value in B1=1-IF(A1="No",0.05,0)-IF(A2="No",0.2,0)-IF(A3="No";0.2,0)
Format B1 as percentage

If by any change total deduction can be more than a 100 and you want to avoid that, try =MAX(0,1-IF(A1="No",0.05,0)-IF(A2="No",0.2,0)-IF(A3="No";0.2,0))

Hope it helps

 

[SMAsh67]

Yes, very helpful! Thank you