Multiple Drop Down Menu to calculate a percentage

I have a bunch of cells that use a drop down menu validation from a different set of cells so

Legend (this are in cells $A$7:$A$11)
NA - Not Applicable
0 - Not attempted
1 - Not Yet Competant
2 - Satisfactory
3 - Exceedes Expectations


Other cells have Data Validation using Allow List =$A$7:$A$11 to make the drop down menu option appear. What I would like to do is add a 'hidden number' on cells A8:A11 (A8=0, A9=1, A10=2, A11=3) A7, being NA isn't included in the formula I want to set up.

In cells later on I'd like to make a formula that takes those 'hidden numbers' into account and works out an average. So 'hidden number' / 'potential total' and make the cell display as a percentage, the 'potential total would have to take into consideration that a value has been set, but not use that value, and times the number of values set by three.

Any ideas, I can run simple macros and can figure stuff out via trial and error so any help is appreciated.

Hello verse,

A simple way to hide data on the worksheet is to change the font color to white. This won't prevent the user from seeing the number in formula bar. To do that, you must set the Formula Hidden property for the cell and protect the worksheet.

Sincerely,
Leith Ross

I don't think your quite understanding what I'm trying to achieve, I'm not trying to hide any data, I'm trying to attach a hidden data field to each option selected to make a calculation based on what the user selects.

Hello Verse,

You should post your workbook. This will make it clear as to both what you are doing and also which solutions can be applied. If you are using Excel 2007, post a copy of the workbook in Excel 2003 as well. The more people you make the workbook available to, the more responses you will get.

Sincerely,
Leith Ross

attached sheet in question

This is a Bump

Hello verse,

After downloading the file and re-reading your post several times, I am going to need a short example of what cells are involved, what values they have, and what numbers will be used in your calculation.

Sincerely,
Leith Ross

Cells A8:A10 are used as the 'validation' (Allow: List, Source =$A$8:$A$10) reference for all the B13:F17+B22:F27+B32:F36+B41:F55+B61:F67 cells (IE unlocked cells the user can select a drop down option from.

I want to be able to automatically calculate the cells in field B72:F73. the maths behind this is
Sum of all numbers if column B / total possible numbers in column B, *100 (to displace as a percentage)

The associated value to A08 = 0, to A09 = 1 & to A10 = 3

for example:

If I select '1 - Not Yet Competent' in every field on column B, the score would be 34 (34*3) / 102 * 100 = 33.33%.

Thanks

"can I get another bump bump"

rolf

Hello verse,

I have added the macro below to the attached workbook. It provides the sum of the 0s, 1s, and 3s in columns "B:F". You will need to add some code to the macro to calculate your "percentage". The macro is called from cells "B72:F72".

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Ah, cool, thanks very much for that