Adding up scores in Excel

R

Robert Jones

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?
 
R

Robert Jones

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?
 
R

Rusty

Robert Jones said:
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 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?
Click to expand...
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
 
R

Roger Govier

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
 
B

Bob Phillips

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)
 
R

Roger Govier

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!!!
 
Top