Hi, we are trying to rank our invoices by amount "Year & Year To Date" but have run into a headache!
We managed to rank them via Quarter & Year (as per Col G table below)... and we were also very helpfully given a great tip by "Fluff" which ranks the invoices - but without "skipping numbers"
ie. if two invoice amounts (which are the same) are in second highest position in the rankings, the next highest ranking to apply to an invoice amount would be 3 (not 4).
Please would it be possible to include the ranking done in that way (i.e. without skipping numbers for the rankings) for the YTD by Quarter figures (in Column I)?
Hopefully the table below will show a little bit better what we are trying to work towards:
Column-D: invoice quarter (i.e. "1" = months 1-3, "2" = months 4-6, "3" = months 7-9, "4" = months 9-12)
Column-F: Ranking Period-01: Invoice / Year
Column-G: Ranking (without skipping numbers) for the Year & Quarter [sorted using Fluff's very, very handy
Column-H: Ranking Period-02: Invoice / YTD by Qr
Column-I: Ranking (without skipping numbers) for the Year & YTD (by Quarter): done manually!
We managed to rank them via Quarter & Year (as per Col G table below)... and we were also very helpfully given a great tip by "Fluff" which ranks the invoices - but without "skipping numbers"
ie. if two invoice amounts (which are the same) are in second highest position in the rankings, the next highest ranking to apply to an invoice amount would be 3 (not 4).
Please would it be possible to include the ranking done in that way (i.e. without skipping numbers for the rankings) for the YTD by Quarter figures (in Column I)?
Hopefully the table below will show a little bit better what we are trying to work towards:
Column-D: invoice quarter (i.e. "1" = months 1-3, "2" = months 4-6, "3" = months 7-9, "4" = months 9-12)
Column-F: Ranking Period-01: Invoice / Year
Column-G: Ranking (without skipping numbers) for the Year & Quarter [sorted using Fluff's very, very handy
Column-H: Ranking Period-02: Invoice / YTD by Qr
Column-I: Ranking (without skipping numbers) for the Year & YTD (by Quarter): done manually!
| invoices-ranked-by-YTD-01-question.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Invoice Number | Invoice Amt | Invoice Date | Invoice Quarter | Invoice Year | Ranking Period-01: Year & Qr | Period-01 Invoice Rank (w/o skipping numbers) For Yr & Quarter | Ranking Period-01: Year & YTD by Quarter | Period-02 Invoice Rank (w/o skipping numbers) For Yr & YTD (by Quarter) | ||
| 2 | 7170 | 1,235 | 13/02/2024 | 1 | 2024 | 2024 / QR 1 | 1 | 2024 / Q1 | 1 | ||
| 3 | 7192 | 800 | 15/03/2024 | 1 | 2024 | 2024 / QR 1 | 2 | 2024 / Q1 | 2 | ||
| 4 | 8001 | 975 | 13/07/2024 | 3 | 2024 | 2024 / QR 3 | 1 | 2024 / Q1-Q2-Q3 | 2 | ||
| 5 | 8112 | 600 | 13/08/2024 | 3 | 2024 | 2024 / QR 3 | 2 | 2024 / Q1-Q2-Q3 | 4 | ||
| 6 | 8242 | 550 | 15/10/2024 | 4 | 2024 | 2024 / QR 4 | 3 | 2024 / Q1-Q2-Q3-Q4 | 7 | ||
| 7 | 8980 | 1,900 | 28/11/2024 | 4 | 2024 | 2024 / QR 4 | 2 | 2024 / Q1-Q2-Q3-Q4 | 2 | ||
| 8 | 8888 | 2,500 | 20/11/2024 | 4 | 2024 | 2024 / QR 4 | 1 | 2024 / Q1-Q2-Q3-Q4 | 1 | ||
| 9 | 2785 | 1,150 | 31/10/2012 | 4 | 2012 | 2012 / QR 4 | 1 | 2012 / Q1-Q2-Q3-Q4 | 1 | ||
| 10 | 3222 | 750 | 21/05/2013 | 2 | 2013 | 2013 / QR 2 | 1 | 2013 / Q1-Q2 | 1 | ||
| 11 | 4980 | 2,625 | 19/03/2015 | 1 | 2015 | 2015 / QR 1 | 3 | 2015 / Q1 | 3 | ||
| 12 | 4955 | 2,800 | 24/02/2015 | 1 | 2015 | 2015 / QR 1 | 1 | 2015 / Q1 | 1 | ||
| 13 | 4921 | 2,700 | 05/01/2015 | 1 | 2015 | 2015 / QR 1 | 2 | 2015 / Q1 | 2 | ||
| 14 | 5140 | 2,600 | 10/05/2015 | 2 | 2015 | 2015 / QR 2 | 2 | 2015 / Q1-Q2 | 4 | ||
| 15 | 5145 | 2,950 | 24/06/2015 | 2 | 2015 | 2015 / QR 2 | 1 | 2015 / Q1-Q2 | 1 | ||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D15 | D2 | =ROUNDUP(MONTH(C2)/3,0) |
| E2:E15 | E2 | =YEAR(C2) |
| F2:F15 | F2 | =CONCATENATE(E2," / QR ",D2) |
| G2:G15 | G2 | =XMATCH(B2,SORT(UNIQUE(FILTER($B$2:$B$20,$F$2:$F$20=F2)),,-1)) |
| H2:H15 | H2 | =CONCATENATE(E2," / ",IF(D2=1,"Q1",IF(D2=2,"Q1-Q2",IF(D2=3,"Q1-Q2-Q3",IF(D2=4,"Q1-Q2-Q3-Q4","error"))))) |
