G
Gary Smith
(Advanced Excel) Figuring out a formula of continuous progression trying tocreate 19 Formulas into One Stable Formula
Here's my exact problem
Top Cell B / Cell C / Cell D / Cell E /
Row 2 B / Empty / B / (Place Formula here in Cell E2)
Row 3 B / Empty / B / (Place Formula here in Cell E3)
Row 4 B / Empty / P / (Place Formula here in Cell E4)
Row 5 P / Empty / P / (Place Formula here in Cell E5)
Row 6 B / Empty / B / (Place Formula here in Cell E6) Etc.
I want a formula that can calculate each situation using the cell above itself to create its answer.(and then the formula can be carried or dragged down to all the rows (Hundreds of rows down the page) with multiple answers depending upon what (Cell B and Cell D) Say.
For example
Row 2 (Cell E2) will always start with the answer of 1
Row 3 (Cell E3) will take that answer of (1) and either subtract a (1) to it if both the letters in (Cell B and Cell D) are the same. Or if the Letters in (Cell B and Cell D) are not the same then increase the number or decrease the number itself by either a plus 1 or a -1 to the above cell in the same Row Below depending on whether or not the number itself is a minus number or a plus number where you grabbed your figures from.
So Here's the Addition or Subtraction Table I'm trying to figure out in this particular formula
1. If the cell above itself in (Cell E Only) is a 1 and if the Letters are the same in Row (Cell B and Cell D) then the answer would stay a 1 (No addition or subtraction needed) For that particular equation.
2. If the cell above itself in (Cell E Only) is a 2 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (2-1= 1) reverting back to a 1 For that particular equation.
3. If the cell above itself in (Cell E Only) is a 3 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (3-1=2) reverting back to a 2 For that particular equation.
4. If the cell above itself in (Cell E Only) is a 5 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (5-4=1) reverting back to a 1 For that particular equation.
5. However If the cell above itself in (Cell E Only) is a 1 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 1 listed in the above cell would be subtracted by -2 Leaving a formula answer (1-2=-1) in the next cell below it of -1 For that particular equation.
6. However If the cell above itself in (Cell E Only) is a 2 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 2 listed in the above cell would be subtracted by -3 Leaving a formula answer (2-3=-1) in the next cell below it of -1 For that particular equation.
7. However If the cell above itself in (Cell E Only) is a 3 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 3 listed in the above cell would be subtracted by -5 Leaving a formula answer (3-5=-2) in the next cell below it of -2 For that particular equation.
8. However If the cell above itself in (Cell E Only) is a 5 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 5 listed in the above cell would be subtracted by -4 Leaving a formula answer (5-4=1) in the next cell below it of 1 For that particular equation.
9. If the cell above itself in (Cell E Only) is a 0 (Zero) and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (0+1=1) reverting back to a 1 For that particular equation.
10. If the cell above itself in (Cell E Only) is a -1 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (-1+2=1) reverting back to a 1 For that particular equation.
11. If the cell above itself in (Cell E Only) is a -2 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (-2+3=1) reverting back to a 1 For that particular equation.
12. If the cell above itself in (Cell E Only) is a -3 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (-3+5=2) reverting back to a 2 For that particular equation.
13. If the cell above itself in (Cell E Only) is a 5 and if the Letters arethe same in Row (Cell B and Cell D) then the answer would be (-5+8=3) reverting back to a 3 For that particular equation.
14. If the cell above itself in (Cell E Only) is a 0 (Zero) and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then the answer would be (0-0=0) staying the same number as (0) (Zero) For that particular equation.
15. However If the cell above itself in (Cell E Only) is a -1 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -1 listed in the above cell would be subtracted by -1 Leaving a formula answer (-1-1=-2) in the next cell below it of -2 For that particular equation.
16. However If the cell above itself in (Cell E Only) is a -2 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -2 listed in the above cell would be subtracted by -1 Leaving a formula answer (-2-1=-3) in the next cell below it of -3 For that particular equation.
17. However If the cell above itself in (Cell E Only) is a -3 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -3 listed in the above cell would be subtracted by -2 Leaving a formula answer (-2-3=-5) in the next cell below it of -3 For that particular equation.
18. However If the cell above itself in (Cell E Only) is a -5 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -5 listed in the above cell would be Added by a plus 5 Leaving a formula answer (-5+5=0) in the next cell below it of plus 0 For that particular equation.
19. However If the cell above itself in (Cell E Only) is a 0 (Zero) and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 0 (Zero) listed in the above cell would be Added by a plus 0 Leaving a formula answer thesame (0+0=0) in the next cell below it of 0 (Zero) For that particular equation.
Thanks for any help you can give me on this project with creating a stable formula.
Gary
Here's my exact problem
Top Cell B / Cell C / Cell D / Cell E /
Row 2 B / Empty / B / (Place Formula here in Cell E2)
Row 3 B / Empty / B / (Place Formula here in Cell E3)
Row 4 B / Empty / P / (Place Formula here in Cell E4)
Row 5 P / Empty / P / (Place Formula here in Cell E5)
Row 6 B / Empty / B / (Place Formula here in Cell E6) Etc.
I want a formula that can calculate each situation using the cell above itself to create its answer.(and then the formula can be carried or dragged down to all the rows (Hundreds of rows down the page) with multiple answers depending upon what (Cell B and Cell D) Say.
For example
Row 2 (Cell E2) will always start with the answer of 1
Row 3 (Cell E3) will take that answer of (1) and either subtract a (1) to it if both the letters in (Cell B and Cell D) are the same. Or if the Letters in (Cell B and Cell D) are not the same then increase the number or decrease the number itself by either a plus 1 or a -1 to the above cell in the same Row Below depending on whether or not the number itself is a minus number or a plus number where you grabbed your figures from.
So Here's the Addition or Subtraction Table I'm trying to figure out in this particular formula
1. If the cell above itself in (Cell E Only) is a 1 and if the Letters are the same in Row (Cell B and Cell D) then the answer would stay a 1 (No addition or subtraction needed) For that particular equation.
2. If the cell above itself in (Cell E Only) is a 2 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (2-1= 1) reverting back to a 1 For that particular equation.
3. If the cell above itself in (Cell E Only) is a 3 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (3-1=2) reverting back to a 2 For that particular equation.
4. If the cell above itself in (Cell E Only) is a 5 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (5-4=1) reverting back to a 1 For that particular equation.
5. However If the cell above itself in (Cell E Only) is a 1 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 1 listed in the above cell would be subtracted by -2 Leaving a formula answer (1-2=-1) in the next cell below it of -1 For that particular equation.
6. However If the cell above itself in (Cell E Only) is a 2 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 2 listed in the above cell would be subtracted by -3 Leaving a formula answer (2-3=-1) in the next cell below it of -1 For that particular equation.
7. However If the cell above itself in (Cell E Only) is a 3 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 3 listed in the above cell would be subtracted by -5 Leaving a formula answer (3-5=-2) in the next cell below it of -2 For that particular equation.
8. However If the cell above itself in (Cell E Only) is a 5 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 5 listed in the above cell would be subtracted by -4 Leaving a formula answer (5-4=1) in the next cell below it of 1 For that particular equation.
9. If the cell above itself in (Cell E Only) is a 0 (Zero) and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (0+1=1) reverting back to a 1 For that particular equation.
10. If the cell above itself in (Cell E Only) is a -1 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (-1+2=1) reverting back to a 1 For that particular equation.
11. If the cell above itself in (Cell E Only) is a -2 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (-2+3=1) reverting back to a 1 For that particular equation.
12. If the cell above itself in (Cell E Only) is a -3 and if the Letters are the same in Row (Cell B and Cell D) then the answer would be (-3+5=2) reverting back to a 2 For that particular equation.
13. If the cell above itself in (Cell E Only) is a 5 and if the Letters arethe same in Row (Cell B and Cell D) then the answer would be (-5+8=3) reverting back to a 3 For that particular equation.
14. If the cell above itself in (Cell E Only) is a 0 (Zero) and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then the answer would be (0-0=0) staying the same number as (0) (Zero) For that particular equation.
15. However If the cell above itself in (Cell E Only) is a -1 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -1 listed in the above cell would be subtracted by -1 Leaving a formula answer (-1-1=-2) in the next cell below it of -2 For that particular equation.
16. However If the cell above itself in (Cell E Only) is a -2 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -2 listed in the above cell would be subtracted by -1 Leaving a formula answer (-2-1=-3) in the next cell below it of -3 For that particular equation.
17. However If the cell above itself in (Cell E Only) is a -3 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -3 listed in the above cell would be subtracted by -2 Leaving a formula answer (-2-3=-5) in the next cell below it of -3 For that particular equation.
18. However If the cell above itself in (Cell E Only) is a -5 and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then -5 listed in the above cell would be Added by a plus 5 Leaving a formula answer (-5+5=0) in the next cell below it of plus 0 For that particular equation.
19. However If the cell above itself in (Cell E Only) is a 0 (Zero) and if the Letters are NOT THE SAME in Row (Cell B and Cell D) then 0 (Zero) listed in the above cell would be Added by a plus 0 Leaving a formula answer thesame (0+0=0) in the next cell below it of 0 (Zero) For that particular equation.
Thanks for any help you can give me on this project with creating a stable formula.
Gary