Hi,
at work I have this report where I need to show stage movement's in our accounts receivable's, basically I need to compare previous month to current mount and see in which stage certain invoice or contract was and in which stage it is in current month.
I have two tables, first one is stage transfer which I extract from account structure, I see per invoice if it is S1, S2 or S3, and the second one is balances for each period for that invoice.
Disregard the first month I would compare it with last mount of previous year
On the bottom of the sheet are transfer which I need to fil.
I hope for some sort of suggestion's, how to sum transfer between stages
Thanks in advance
at work I have this report where I need to show stage movement's in our accounts receivable's, basically I need to compare previous month to current mount and see in which stage certain invoice or contract was and in which stage it is in current month.
I have two tables, first one is stage transfer which I extract from account structure, I see per invoice if it is S1, S2 or S3, and the second one is balances for each period for that invoice.
Disregard the first month I would compare it with last mount of previous year
On the bottom of the sheet are transfer which I need to fil.
I hope for some sort of suggestion's, how to sum transfer between stages
Thanks in advance
| Stage transfer tabel.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 2 | Stage transfer tabel | MONTH PRODUCT | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
| 3 | A | 2 | 3 | 1 | 2 | 3 | 3 | 1 | 3 | 3 | 2 | 2 | 3 | |||
| 4 | B | 3 | 3 | 1 | 3 | 1 | 3 | 2 | 2 | 3 | 2 | 3 | 2 | |||
| 5 | C | 2 | 1 | 2 | 2 | 2 | 2 | 1 | 2 | 3 | 2 | 3 | 2 | |||
| 6 | D | 2 | 2 | 1 | 3 | 2 | 1 | 2 | 2 | 3 | 2 | 3 | 1 | |||
| 7 | E | 1 | 2 | 2 | 1 | 1 | 3 | 3 | 1 | 1 | 2 | 2 | 2 | |||
| 8 | F | 3 | 2 | 3 | 1 | 1 | 1 | 2 | 2 | 1 | 3 | 3 | 3 | |||
| 9 | G | 2 | 2 | 3 | 2 | 3 | 3 | 2 | 1 | 2 | 3 | 1 | 3 | |||
| 10 | ||||||||||||||||
| 11 | Amount transfer tabel | MONTH PRODUCT | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
| 12 | A | 7.834,05 | 6.253,09 | 7.845,92 | 5.028,10 | 4.136,63 | 1.271,94 | 3.785,32 | 2.481,04 | 1.924,52 | 4.438,93 | 2.415,00 | 5.232,81 | |||
| 13 | B | 2.515,56 | 4.769,00 | 3.007,89 | 8.502,49 | 8.283,71 | 6.734,92 | 2.668,75 | 1.742,42 | 3.296,06 | 1.999,48 | 2.459,60 | 3.220,40 | |||
| 14 | C | 911,18 | 1.197,85 | 8.542,38 | 7.151,30 | 1.075,32 | 7.516,59 | 3.480,68 | 5.406,72 | 7.611,40 | 2.265,73 | 5.593,92 | 2.222,45 | |||
| 15 | D | 2.411,45 | 7.397,13 | 4.109,59 | 7.584,43 | 9.525,15 | 6.930,45 | 4.131,66 | 4.310,50 | 8.818,32 | 4.517,44 | 4.864,64 | 1.874,16 | |||
| 16 | E | 3.585,70 | 1.516,87 | 8.616,17 | 5.536,69 | 3.890,40 | 9.481,83 | 1.727,50 | 2.254,42 | 5.209,56 | 6.838,13 | 9.824,19 | 3.544,95 | |||
| 17 | F | 2.268,31 | 3.873,60 | 1.630,11 | 9.308,79 | 6.300,90 | 6.773,85 | 1.040,40 | 669,84 | 3.878,08 | 6.749,09 | 2.573,76 | 3.790,40 | |||
| 18 | G | 8.462,79 | 7.541,18 | 4.236,58 | 4.770,03 | 946,49 | 4.760,55 | 4.075,83 | 2.912,60 | 9.385,75 | 8.850,82 | 8.261,38 | 4.412,73 | |||
| 19 | ||||||||||||||||
| 20 | S1 > S2 | |||||||||||||||
| 21 | S1 > S3 | |||||||||||||||
| 22 | S2 > S1 | |||||||||||||||
| 23 | S2 > S3 | |||||||||||||||
| 24 | S3 >S2 | |||||||||||||||
| 25 | S3 >S1 | |||||||||||||||
Sheet1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3:O9 | D3 | =+RANDARRAY(7,12,1,3,TRUE) |
| D12:O18 | D12 | =+RANDARRAY(7,12,500,10000,) |
| Dynamic array formulas. | ||
