Thread: adding + and - numbers

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.

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!


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Robb27" <Robb27@discussions.microsoft.com> wrote in message
news:98C7CF12-CA2B-4D95-BDAD-FE5DBD5616F6@microsoft.com...
> 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.

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" wrote:

> 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!
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> "Robb27" <Robb27@discussions.microsoft.com> wrote in message
> news:98C7CF12-CA2B-4D95-BDAD-FE5DBD5616F6@microsoft.com...
> > 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.
>
>
>

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Robb27" <Robb27@discussions.microsoft.com> wrote in message
news:02C60006-A52A-4BB1-AE24-16412C14E948@microsoft.com...
> 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" wrote:
>
>> 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!
>>
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>>
>>
>>
>> "Robb27" <Robb27@discussions.microsoft.com> wrote in message
>> news:98C7CF12-CA2B-4D95-BDAD-FE5DBD5616F6@microsoft.com...
>> > 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.
>>
>>
>>

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

Thanks Arvi!

"Arvi Laanemets" wrote:

> 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.
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> "Robb27" <Robb27@discussions.microsoft.com> wrote in message
> news:02C60006-A52A-4BB1-AE24-16412C14E948@microsoft.com...
> > 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" wrote:
> >
> >> 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!
> >>
> >>
> >> --
> >> Arvi Laanemets
> >> ( My real mail address: arvi.laanemets<at>tarkon.ee )
> >>
> >>
> >>
> >> "Robb27" <Robb27@discussions.microsoft.com> wrote in message
> >> news:98C7CF12-CA2B-4D95-BDAD-FE5DBD5616F6@microsoft.com...
> >> > 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.
> >>
> >>
> >>
>
>
>