(Advanced Excel) Figuring out a formula of continuous progression trying to create 19 Form

[Zotah]

(Advanced Excel) Figuring out a formula of continuous progression trying to create 19 Formulus into One 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 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.

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

Zotah

[daffodil11]

Are you able to attach a sample? Advanced Excel requires pretty clear cut examples and desired result.

Can you show sample data, and what it might look like when done, minus the formulas? I'm thinking using a small table to lookup the variable results would be the swiftest solution but I'd need to see your setup first to put it all together.

[Zotah]

Sure Here'S an exact example of how it would look with the answers of the formula giving all the correct answers

A B C D E F G H I J K L M N O



2 B * B 1 B 1 B 1 P -1 P -1 P -1

3 B * B 1 B 1 B 1 B 2 B 2 B 2

4 P * P 1 B -1 B -1 B -1 B -1 B -1

5 B * P -1 P -2 B 2 P -2 B 2 B 2

6 P * B -2 P 3 P 1 P 3 P 1 B -1

7 P * B -3 P 2 P 1 B -2 P 1 P 2

8 B * P -5 B 1 P -1 B 3 P -1 P -1

9 B * P 0 B 1 P -2 P -2 B 2 P -2

10 P * B 0 B -1 B -3 P 3 B -1 P 3

11 B * B 0 P -2 B 5 B 2 B 2 B 2

12 B * P -1 P -3 B 1 B 1 P -1 B 1

13 P * P 2 P 5 B -1 P 1 P 2 B -1

14 B * B 1 B 1 P -2 P -1 P -1 B 2

15 B * B 1 B 1 P -3 B -2 B -2 P -1

16 B * P -1 B -1 P -5 B -3 B -3 P -2

17 B * P -2 P -2 P 0 P -5 B 5 P -3

18 B * B 3 P 3 B 0 P 0 P -1 P -5

19 P * B -2 P -2 B -1 B 0 P 2 B 0

20 P * P 3 B -3 B -2 B 0 P 1 B 0

21 B * P -2 B 5 B 3 P 0 B 1 B 0

22 P * B -3 B -1 P 2 P 0 B -1 B -1

23 P * B -5 P 2 P 1 B -1 B -2 P 2

24 P * P 0 P 1 P 1 B -2 P 3 P 1

25 P * P 1 P 1 P 1 P 3 P 2 P 1

26 B * B 1 B 1 B 1 P -2 P -1 P -1

27 P * B -1 B -1 B -1 B -3 B 2 B -2

28 B * P -2 B 2 B 2 B 5 B -1 B 3

29 P * P 3 P 1 B -1 P 1 B 2 B -2

30 B * B 2 P -1 P -2 P -1 P -1 B 3

31 B * B 1 P -2 P -3 B 2 P -2 P -2

32 B * P -1 B 3 P -5 B 1 P -3 P -3

33 B * P -2 B 2 P 0 P -1 B 5 P -5

34 P * B -3 B -1 B 0 P 2 B -1 P 0

35 B * B 5 P -2 B 0 B 1 B 2 B 1

36 P * P 1 P 3 B -1 B -1 P 1 B -1

37 P * P 1 P 2 B -2 P 2 P 1 B -2

38 B * B 1 B 1 P -3 P -1 P -1 B 3

39 P * B -1 B -1 P 5 B -2 B -2 P 2

40 B * P -2 B 2 P -1 B 3 B 3 P -1

41 P * P 3 P 1 P 1 P 2 B -2 P 2

42 B * B 2 P -1 B 1 P -1 P -3 P -1

43 P * B -1 P 2 B -1 B -2 P 5 B -2

44 B * P -2 B 1 B 2 B 3 P -1 B 3

45 B * P -3 B 1 B 1 P -2 B 2 B 2

46 P * B -5 B -1 P 1 P 3 B -1 B -1

47 B * B 0 P -2 P -1 B 2 B 2 P -2

48 B * P -1 P -3 P -2 B 1 P -1 P -3

49 P * P 2 P 5 P 3 P 1 P 2 P 5

50 B * B 1 B 1 B 2 P -1 P -1 P -1



So if Columns B and D will give the answer for The Formula to go into Column E

then I will use the same formula created to find the answer for Columns B and F to be calculated for Column G

then I will use the same formula again to create to find the answer for Columns B and H to Be calculated for Column I

then I'll get answer for Columns B and J to be calculated for Column K

and B and L to be calculated for Column M

and lastly B and N to be calculated for Column O

In the first question here I only asked for the answer coming from B and D to solve for E. But as you can see After I get the forumla for E I want to transpose it to all the rest of the 6 columns.

Let me know if this helps in figuring out the formula

Thanks
Zotah

[FDibbins]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Zotah]

Forum Excel Example Worksheet.xlsx

Let me know if you get this workbook I made with comments

Page 2 will have the attributes of each formula in the excel file as well.


Zotah