Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding + and - numbers
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(A20,A2<11)),1-AND(A26,A2<10)-2*OR(A2=10,A2="A"),"") or =IF(AND(A20,A2<7),1,IF(AND(A26,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<attarkon.ee ) "Robb27" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding + and - numbers
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(A20,A2<11)),1-AND(A26,A2<10)-2*OR(A2=10,A2="A"),"") or =IF(AND(A20,A2<7),1,IF(AND(A26,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<attarkon.ee ) "Robb27" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding + and - numbers
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<attarkon.ee ) "Robb27" wrote in message ... 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(A20,A2<11)),1-AND(A26,A2<10)-2*OR(A2=10,A2="A"),"") or =IF(AND(A20,A2<7),1,IF(AND(A26,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<attarkon.ee ) "Robb27" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding + and - numbers
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<attarkon.ee ) "Robb27" wrote in message ... 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(A20,A2<11)),1-AND(A26,A2<10)-2*OR(A2=10,A2="A"),"") or =IF(AND(A20,A2<7),1,IF(AND(A26,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<attarkon.ee ) "Robb27" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numbers from a list over a specific value | Excel Discussion (Misc queries) | |||
Adding to a column of numbers | Excel Worksheet Functions | |||
Adding new numbers as I type without duplicates from Sheet1,ColumnA to Sheet2,ColumnA | Excel Worksheet Functions | |||
Adding numbers to current numbers | Excel Worksheet Functions | |||
adding only positive numbers | Excel Discussion (Misc queries) |