Excel value

[Haseena]

I am trying to create a scoring system with a 100% end value, but I need to know how to adjust the 100% down according to the score. It's a test, actually. Right now it is set at zero % to begin with, but I think it should begin with 100% and go down from there, because the grading system has a score card from 1-4 and then a "NA" category. The way it is now, the "NA" has no value, so what happens is that it actually scores lower instead of giving no value either way. Hopfully, this is not too confusing. Can anyone help?
Thanks,
Haseena

[Sisyphus]

Hi,
Yes, it is a bit confusing.
But for a scoring system you will need at least 2 cells, one which contains the score and the other to contain the valuation of that score, for example, expressed as a percentage. Say, the score is in A1 and the result in B1. So, A1 is the cell that you write in. B1 holds the formula to create the result.

First of all you need a system by which you score. You say, you have 1-4 plus NA. Why not stick with this, except that you enter NA as 0 (zero). I imagine that 1 is the lowest and 4 the highest score. The conversion to percentage would look like this:
1 = 25%
2 = 50%
3 = 75%
4 = 100%
0 = 0% (but you could give another value)
to achieve this result you need to enter this formula in B1:

Code: Select all

= $A1 * 0.25


In order to see the correct result you must format B1 as "Percentage". Right-click on B1 and select "Format Cells". On the "Numbers" Tab select "Percentage". Now, as you enter a value in A1 the percentage shown in B1 will change.

If you want to prevent some one entering a 5 in A1 (which would show 125% score) you may limit the entries in that cell. Place the cursor in A1 and select "Validation" from the "Data" dropdown in the Toolbar. On the "Settings" tab select "Whole number" in the "Allow" box. In the "Data" box select "between". In the "Maximum" and "Minimu," boxes enter 4 and 0 respectively. Press OK. From now on you can only enter numbers between 0 and 4 in A1.

I hope this will help you develop your worksheet.

[Haseena]

Thank you for your response. Actually, we have already done everything you said, except that the final score is set at zero and then increases from there. It is giving us a flawed evaluation, so I was thinking that we should format it to begin at 100% and go down according to the score. I have attached the chart here for you to see what I mean. I truely wish there were someone in this office that knew what to do. It would make life easier. I am working with another person and we are so frustrated over this.

I have made a document highlighting the chart and the changes I'd like to implement. Is there a way to send it to you so you can look it over for me? I would soooooooo appreciate it.

Thank you so much for your help.
Haseena

[Sisyphus]

Hi,
If you want me to look at the Excel sheet please post it in xls format so that I can open it with Excel 2003.
However, the problem you describe originates from the AVERAGE function. AVERAGE can't deal with NA. So you should make your own total and divisor. Use SUM(A1:A10) to make the total score. Then use COUNTIF(A1:A10,<>0) to count the number of scores in the same range, presuming that NA is zero and there are no other entries. The division will result in an error if COUNTIF is zero. Therefore you divide into one if there are no scores. The whole formula will look like this:

Code: Select all

=SUM(A1:A10)/IF(COUNTIF(A1:A10,<>0)>0,COUNTIF(A1:A10,<>0),1))


Set the range as required. If it should give you an error test the COUNTIF part separately. It may want the condition in inverted commas, like this: COUNTIF(A1:A10,"<>0").
I hope that helps.
If it doesn't, post your sheet. Don't throw it away quite yet! It looks lovely.

[Haseena]

LOL, now I've gotten myself wayyyyy over my head. I have attached the excel sheet here for you to look over. Thanks for the compliment, but I did not do it all on my own. I had help, and we are both at a crux in the road. I am also posting internal business data that is not supposed to be public, but what can I do? I guess, I am not as geeky as my kids say I am, or I could figure this thing out.
You have been very helpful, and I can't begin to tell you how much I appreciate it. Let me know what you think I could do.
Thanks,
Haseena

[Sisyphus]

Hi,

Have a look if this function, placed in cell K33, will do what you want:

Code: Select all

=SUM(K$5:K$31)/IF(COUNTIF(K$5:K$31,">0"),COUNTIF(K$5:K$31,">0"),1)/100


All the best!

[Haseena]

Okay, well, I tried that function and it's still doing the same thing as before. Should I just give up?

[Sisyphus]

Hi,

Of course, we don't give up. We fix it!

Problem is, I don't quite understand the problem. On the attached sheet I have inserted my formula at the bottom below yours. I have also filled in some data. As you can see, my formula doesn't produce the same result as yours. Perhaps you even like the result of your formula better. Please decide which one is doing the correct job or, using the data I have keyed in, work out the correct result and describe how you arrive at it.

Now, I have chosen data with an average of 2, not counting the NA. This seems to work out as 33% by your formula, and these are the same 33% my formula shows at the bottom because it ignores tests not taken. The overall formula, in plain English, shows the average score of those tests taken, not heeding those that were not taken (yet). If you fill another group with twos my total average will not change. Yours will, because it takes evaluations not yet in evidence into account. Only one of these approaches can be right.

Concentrating on the percentages in the K column I notice that they change when I replace a NA with the average (2). If this was your concern in the first place then I have been barking up the wrong tree. I have entered this formula in the topmost cell in column K.

Code: Select all

=SUM(H5:J7)/(MAX(C5:F5)*COUNTIF(H5:J7,"<>NA"))*100


This formula ignores all NAs and just counts the actual results. I have adapted this formula to the requirements of rows 25 which I thought might be different, but actually it isn't. So, if you like this formula you may be able to adapt it to other cells in the K column by comparing how I did the adaptation in row 25.

For future development you need to know that the formula selects the maximum value in only one row (selecting more than one row doesn't make a difference), presuming the others to be the same. So, please avoid having different valuations within any one group in future. That number is multiplied by the number of actual calls (excluding NA calls).

Let me know if we need to tweak some more.
Have a great day!

[Haseena]

Thank you for working on this. I think we are on the right track, however, when everything is NA or 4's and NA it should total 100% in the end. That is the result I am looking for. I just can't figure out how to do that. Yes, I want NA to not effect the value or the total, but if you put NA in every category, for example, the score should total 100%. You know, it's like writing a song with limited music ability. You can see it in your head, but just can't make it come out right. Where should I go from here? You seem to be the only one that can help me at all. My partner in crime here has pretty much given up, but I know it's possible to do this. What do you think?

Thanks a million,
Haseena

[Sisyphus]

Hi,
Why is it that I have to pay more tax each time I mark a "N/A" checkbox on their form while you give your candidates full marks for not being applicable?

Try this one for luck:

Code: Select all

=IF(COUNTIF(H5:J7,"=NA")=COUNTA(H5:J7),1,SUM(H5:J7)/(MAX(C5:F5)*COUNTIF(H5:J7,"<>NA")))*100


It works precisely same as the last one but gives royal treatment to the guy who flies above the clouds.