Range including *

I am making a scoring sheet, which will allow the user to score skills on a range of 1-5. I set the range and that works fine, but now we want to use something like '*' to indicate not scored. I had used a 0 for not scored, but since we are averaging the scores that doesn't work.

Is there a way to set a range of 1-5, but also accept something like '*'?

Thanks very much.

Hi Bobelmore,

Excel would not use the * value in cells to adjust the average so you can use this as your N/A value.

What are you using to create you range?

Named ranges? Data Validation?

I think we might need an example worksheet to see your problem as I can't work out what the issue actually is

DataVal.PNG

I am using Data Validation to restrict the input to whole numbers 1-5. I don't see a way to allow the asterisk.

Using a list is a good idea, but the asterisk is then counted as zero when averaging.

Glenn, thank you. I looked at your example, but that is not how I had mine set up.

I have attached an example of what I am trying to do. It works fine, except that I cannot enter an asterisk, or any other character to signify not yet scored.

Thanks for continuing with this Glenn, I suspected it was to do with data validation as the OP was steering us towards the calculation being an issue which I wouldn't expect it to be.

Bobelmore hopefully this now has resolved your issue?

Hello, and thanks for all your help.

Your example works exactly as I had hoped. However, when I used the same approach in my scoring sheet the asterisk is still being computed as zero. I am trying to figure out how they are different.

Thanks.