(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O

[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

 

[Don Guillett]

(Advanced Excel) Figuring out a formula of continuous progression trying to create 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 CellE2)

Row 3 B / Empty / B / (Place Formula here in CellE3)

Row 4 B / Empty / P / (Place Formula here in CellE4)

Row 5 P / Empty / P / (Place Formula here in CellE5)

Row 6 B / Empty / B / (Place Formula here in CellE6) 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 answersdepending 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) toit 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 thesame 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 thenext 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 thenext 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 thenext 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) inthe 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) inthe 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) inthe 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) inthe 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 particularequation.



Thanks for any help you can give me on this project with creating a stable formula.



Gary

I would recommend a vba function using select case to solve this

 

[Gary Smith]

(Advanced Excel) Figuring out a formula of continuous progression trying to create 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 CellE2)

Row 3 B / Empty / B / (Place Formula here in CellE3)

Row 4 B / Empty / P / (Place Formula here in CellE4)

Row 5 P / Empty / P / (Place Formula here in CellE5)

Row 6 B / Empty / B / (Place Formula here in CellE6) 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 answersdepending 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) toit 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 thesame 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 thenext 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 thenext 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 thenext 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) inthe 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) inthe 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) inthe 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) inthe 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 particularequation.



Thanks for any help you can give me on this project with creating a stable formula.



Gary

 

[Alex Plantema]

Create a table, in e.g. G2:I12 with contents:

-5 3 0
-4 -4 -4
-3 2 -5
-2 1 -3
-1 1 -2
0 1 0
1 1 -1
2 1 -1
3 2 -2
4 4 4
5 1 1

and use

=IF(B3=D3;VLOOKUP(E2;G2:H12;2);VLOOKUP(E2;G2:I12;3))

in E3.

 

[Alex Plantema]

Create a table, in e.g. G2:I12 with contents:

-5 3 0
-4 -4 -4
-3 2 -5
-2 1 -3
-1 1 -2
0 1 0
1 1 -1
2 1 -1
3 2 -2
4 4 4
5 1 1

and use

=IF(B3=D3;VLOOKUP(E2;G$2:H$12;2);VLOOKUP(E2;G$2:I$12;3))

in E3.

 

[Alex Plantema]

Alex Plantema schreef in

=IF(B3=D3;VLOOKUP(E2;G$2:H$12;2);VLOOKUP(E2;G$2:I$12;3))

This is the LibreOffice version, use commas instead of semicolons for Excel.
If you want a shorter formula: =VLOOKUP(E2,G$2:I$12,(B3<>D3)+2)