Excel Problem

[Ice Globe]

I'm having some difficulties creating a new formula by sourcing from a cell using the "IF" formula.

In column A, I have a list of repeating names, in column B I have a rank (1-10), and in column C I have points (using an IF formula to put rank 1 = 10 points, rank 2 = 9 points etc.)

Then I want to create a new formula for when "IF Column A name is _ _ _ _" "SUM Column C", so that only the relevant rows of the same name are totalled.

However, when I carry out the formula, the result will always be 0. I know the formula is pretty much correct, because if I replace Column C (where its trying to source the points value) with actual numbers instead of calculating it from Column B, then the correct totals are appearing.

I'm running Excel 2003, is this a problem with the version of Excel or a formula error?

Hope its not too confusing to understand and many thanks for any help!

 

[dazzerd]

Hi m8 sounds to me you want to use the sumif formula, it would help if you give me an example of the data

 

[mark66]

I am not sure I fully understood your query, as I thought you would want to sum column B, not C?

But I would hazard a guess that in your IF statement, where you are ranking them, are the scores in quotes? If so they will be seen as character and will not sum, perhaps this is the problem?

Eg =IF(A1 ="Sam", "1") should be =IF(A1 ="Sam", 1)

 

[Ice Globe]

Here's an example of the data:

Surveying people's favourite drinks, asking them to list their favourite 10 in rank order. So I have a list of 10 different names per person, but obviously some if not all these names will repeat between different people.
The way I had planned was to reward a rank 1 with 10 points, and a rank 10 with 1 point, which is where my "IF" formula has been used.

Using a long string, the formula to calculate the points is:
=IF(A1=1,"10",IF(A1=2,"9",IF(A1=3,"8",.....IF(A1=10,"1")))))) which works fine and produces the expected points scores.

Now if I want to calculate the total points for each drink, as there are lots I want to use a formula:
=SUMIF(A1:A100,"Lemonade",C1:C100) which I'm hoping will find in the A column all Lemonade entries, and add the relating points score from column C together, giving me the total points value for the drink Lemonade.

Now the problem is that the result comes out as 0, with no formula errors. But I can get it to work as I would expect if I put actual numbers into the column C instead of getting excel to work out the IF formula of turning rank into points. But obviously I don't want to manually type all the rank to point calculations down the list.

 

[mark66]

As said before, I am quite certain you need to remove the quotes from your IF string, so it would be:

=IF(A1=1,10,IF(A1=2,9,IF(A1=3,8,.....IF(A1=1 0,1))))))

At the moment the scores you are rewarding are character and therefore will not sum.

Edit: If this isn't the problem then I suggest saving your spreadsheet and host it somewhere so we can download and take a better look.

 

[Ice Globe]

As said before, I am quite certain you need to remove the quotes from your IF string, so it would be:

=IF(A1=1,10,IF(A1=2,9,IF(A1=3,8,.....IF(A1=1 0,1))))))

At the moment the scores you are rewarding are character and therefore will not sum.

Edit: If this isn't the problem then I suggest saving your spreadsheet and host it somewhere so we can download and take a better look.

Apologies, missed your first reply, removing the quotes fixed it, thank you

 

[Namsnik]

Glad you solved your problem. A simpler approach might have been
Score=11-Rank

I am suprised that your approach worked as you would need a lot of nested IFs and help says " A formula can contain up to seven levels of nested functions".

And to get the total scores a pivot table would be much easier!
Make sure your database has a single row of column headings (field names) with a heading for each column. Highlight the database and select Data,Pivottable report.....