Trying to create 19 formulas into 1 formula
(Advanced Excel) Figuring out a formula of continuous progression trying to create 19 Formulas into One Stable Formula
Forum Excel Example Worksheet.xlsx
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 are the 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 the same (0+0=0) in the next cell below it of 0 (Zero) For that particular equation.
Here's my Excel worksheet that has every already in the cells as the would be if the formula was created for it. This way you can see exactly how it should look after the formula is inserted into column E
Forum Excel Example Worksheet.xlsx
When checking the worksheet be sure to take a look at the comments I created for each Row for each cell in (Column Cell E)
Thanks for any help you can give me on this project with creating a stable formula.
Zotah
Reply With Quote
Bookmarks