Adding up scores in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert Jones - 21 Jan 2006 20:26 GMT

I have a series of 25 items which people have scored 1=best, 10=worst etc.

I want to find the most popular item, and have entered the following formula
against F2:

=IF(B2=0,0,25-B2)

The idea being that if there is no vote, then the score should be 0, but if
there is a vote then the score should 25-(the vote)

So if someone has voted an item 1, which is the top vote, then it should get
a score of 24, 2 gets a score of 23, 3 gets a score of 22 etc, but items
with no vote still get a score of 0.

The problem is when I enter this formula I get #VALUE! because it doesn't
like the 25-B2 entry.

How can I get the result I want?

Reply to this Message

Robert Jones - 21 Jan 2006 20:41 GMT

My daughter has just pointed out that the reason 25-B2 isn't working is
because all the scores have a space next to the number.

We have had to go through all the scores removing the space and then the
formulae worked.

Isn't it a bit crap that Excel doesn't recognise the number if there is a
space next to it?

>I have a series of 25 items which people have scored 1=best, 10=worst etc.
>
[quoted text clipped - 14 lines]
>
> How can I get the result I want?

Reply to this Message

Roger Govier - 21 Jan 2006 21:27 GMT

Hi Robert

You can force Excel to treat a text number (which is what a space after
the number would signify), by using the double unary minus
=25-(--B2)
or multiplying by 1
=25-B25*1

If you have the task of taking the spaces out again, you could type a 1
in an empty cell on your sheet, copy it, then mark the range of data
which is text>Paste Special>Multiply

Signature

Regards

Roger Govier

> My daughter has just pointed out that the reason 25-B2 isn't working
> is because all the scores have a space next to the number.
[quoted text clipped - 24 lines]
>>
>> How can I get the result I want?

Reply to this Message

Bob Phillips - 21 Jan 2006 22:11 GMT

Roger,

Doesn't 25-B2 work for you, even when B2 has a trailing space?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> Hi Robert
>
[quoted text clipped - 41 lines]
> >>
> >> How can I get the result I want?

Reply to this Message

Roger Govier - 21 Jan 2006 22:23 GMT

Hi Bob

Yes it does. The brain was in idle tickover mode, and the OP saying his
formula wasn't working because of spaces just brought out the fairly
standard response for dealing with text numbers.
Now that you've woken me up again, time to get on with some work!!!

Signature

Regards

Roger Govier

> Roger,
>
[quoted text clipped - 62 lines]
>> >>
>> >> How can I get the result I want?

Reply to this Message

Bob Phillips - 22 Jan 2006 10:12 GMT

As I thought. So I wonder what the real problem is?

Bob

> Hi Bob
>
[quoted text clipped - 18 lines]
> >
> > (remove nothere from the email address if mailing direct)

Reply to this Message

Rusty - 21 Jan 2006 20:49 GMT

>I have a series of 25 items which people have scored 1=best, 10=worst etc.
>
[quoted text clipped - 7 lines]
>
> How can I get the result I want?

I just copied and pasted your formula into Excel and it works perfectly.

Are you sure you entered it correctly in Excel? Try copying and pasting it
from your post.

Ken

Reply to this Message