B
Baffled
I have a table that uses data from two separate tables in its formulas.
However, because the calculated table does not correspond row by row to the
source tables, the fill down of the formulas in rows of 3 for Week 2 (rows
17,18,19) and the next 50 weeks will not work properly. I want to fill down
rather than type the formulas in for each week. I tried making the row or
column static, but that still lets the row or column that changes change by
three and not by 1. I looked into OFFSET, but I can't figure out how that
would work. Can anyone help me?
B C D E F G
SOURCE TABLE 1
Goal Comp % Orders Orders % Ticket Avg
4 Week1 10808 0.112 663 0.157 16.302
5 Week2 10900 0.115 672 0.158 17.5565
B C D E F G
SOURCE TABLE 2
Week1 Week2
9 Quantity 9713.15 10236.33
10 Orders 573 594
B C D E F G
CALCULATED TABLE
Goal Comp % Orders Orders % Ticket Avg
14 Week1 10500 =(C14-C9)/C9 =C14/G14 =(E14-C10)/C10 16.5
15 Actual =C4 =D4 =E4 =F4 =G4
16 Diff =C15-C14 =D15-D14 =E15-E14 =F15-F14 =G14-G15
17 Week 2
18 Actual
19 Diff
However, because the calculated table does not correspond row by row to the
source tables, the fill down of the formulas in rows of 3 for Week 2 (rows
17,18,19) and the next 50 weeks will not work properly. I want to fill down
rather than type the formulas in for each week. I tried making the row or
column static, but that still lets the row or column that changes change by
three and not by 1. I looked into OFFSET, but I can't figure out how that
would work. Can anyone help me?
B C D E F G
SOURCE TABLE 1
Goal Comp % Orders Orders % Ticket Avg
4 Week1 10808 0.112 663 0.157 16.302
5 Week2 10900 0.115 672 0.158 17.5565
B C D E F G
SOURCE TABLE 2
Week1 Week2
9 Quantity 9713.15 10236.33
10 Orders 573 594
B C D E F G
CALCULATED TABLE
Goal Comp % Orders Orders % Ticket Avg
14 Week1 10500 =(C14-C9)/C9 =C14/G14 =(E14-C10)/C10 16.5
15 Actual =C4 =D4 =E4 =F4 =G4
16 Diff =C15-C14 =D15-D14 =E15-E14 =F15-F14 =G14-G15
17 Week 2
18 Actual
19 Diff