adding + and - numbers

[Guest]

Hi All:

In row A1 through K1 I have either a positive 1, negative 1 or a 0.
In cell L1 I want to display the total.

The value in A1 through K1 is determined data entered into Row A2 through K2
via a lookup formula. If I type a number 1-6 in cell B2 then A1 will reflect
a 1. If I put in 7-9 in B1 then it puts a 0 value in A1. and if I put in the
letter A or the number 10 in B1 then it puts a -1 in cell A1.
It does this via a lookup formula: VLOOKUP(A2,$N$1:$O$10,2,FALSE) with N1
through N10 is 2 3 4 5 6 7 8 9 10 A
and O1 through O10 is 1 1 1 1 1 1 0 0 0 -1 -1

My main problem after the wordy description is that Cell L1 does not add up
properly. If cells B2 through K2 are 1 through 6 then it adds up fine. If one
of the numbers in cells B2 through K2 are 10 or A then it subtracts 2 from
the total instead of only subtracting 1 (is there something I should be using
besides Sum(A1:K1)
?

Thanks for your help. I am tryingt to write a sheet that computes the
running count using the simple plus minus count system. (for the gamblers of
you out there!)
Let me know if I need to explain differently.

 

[Arvi Laanemets]

Hi

What about cases where some cell in A2:K2 is empty, or there is entered
something different?

And no need for lookup table at all, when calculation rules are so simple.
Into A1 enter
=IF(OR(A2="A",AND(A2>0,A2<11)),1-AND(A2>6,A2<10)-2*OR(A2=10,A2="A"),"")
or
=IF(AND(A2>0,A2<7),1,IF(AND(A2>6,A2<10),0,IF(OR(A2=10,A2="A"),-1,"")))
, and copy it to range A1:K1
Into L1 enter the formula
=SUM(A1:K1)
It's all!

 

[Guest]

Hi Arvi,
Thanks for your reply, and your formula works...but it doesn't. If i enter
the value of 10 in A2 through K2 it displays the correct card value in cells
A1 through K1 AND L1 displays the proper total of -11. Great!
But, if I change the value of one of the cells in row 2 (for example K2)
from a 10 to a value of 2 through a 6, it should change the total in L1 to
-10. Presently, it doesn't, it subtracts 2 from the total.

It should only subtract 1. (because the 2 through 6 cards has a value of +1)
So -11 + 1 should equal -10!
Thanks for looking at this Arvi. Hope I helped you with this explaination.

Rob

 

[Arvi Laanemets]

Hi

Make all cells in row 2 empty. Insert into one cell, p.e. into A2, the value
10. In both A1 and L1 a number -1 is displayed. It's OK?
Change the value in A1 p.e. to 1. In both A1 and L1 a number 1 is now
displayed. It's all correct again, is it? Now calculate the difference
between previous and current sum - what do you get?

Your mistake is, you assumed the new value is added to previous sum. Really,
the sum is recalculated, and the change equals to difference between
previous and new value in row 1.

 

[Guest]

I see. My perspective was wrong. Got it now. <insert sheepish grin here>

Thanks Arvi!