adding if statement results

[Robb27]

I am trying to build a sheet that brings certain values to playing card
values. Example: A deck of playing cards, 2's through 6's = +1. 7's, 8's and
9's have 0 value and 10's and Aces (A) have a value of -1.

I have an IF formula so that when any of these cards 2-Ace (A) is inserted
in a cell, then it returns its appropriate value. It looks like this:

=IF(A2=7,"0",IF(A2=9,"0",IF(A2=8,"0",IF(A2=2,"1",IF(A2=3,"1",IF(A2=4,"1",IF(A2=5,"1",IF(A2=A10,"-1"))))))))

Now, in cell K1, I want to sum the row of newly aquired values (either +1,
-1, or a 0) and I can't get it to work. It won't add the row with =sum

Thanks for your expertise,

Robb

 

[duane]

your if statement is resulting in text so sum will result in error

i suggest taking the "'s out of the formula

=IF(A2=7,"0",IF(A2=9,"0",IF(A2=8,"0",IF(A2=2,"1",IF(A2=3,"1",IF(A2=4,"1",IF(A2=5,"1",IF(A2=A10,"-1"))))))))

also note that the last if statement does not have a result for a2 not
equal to a10

you are also missing test for a2=6 I think

probably much simpler to do via a lookup table - set possible values
for a2 in column1 and desired value in column2 and do a vlookup

 

[Biff]

Hi!

You can shorten that formula to:

=IF(A2="","",IF(A2<=6,1,IF(A2<=9,0,-1)))

When you say that =SUM doesn't work, exactly what doesn't
work? What are you trying to sum? Are you getting error
messages?

Biff

 

[Biff]

P.S.

I just noticed why your sum won't work:

In your formula you have the " " around the numeric
values. Excel treats those values as TEXT, not numeric
numbers. Leave out the quotes and it should be ok but you
should still use the shorter formula I offered.

Biff