CHOOSE

Why it works

CHOOSE does not search for anything. The number in B2 is a position, and the position decides which of the four values is returned. That is why the phase numbers must follow the order of the list in the formula.

More to watch out for

• Put the names in a different order than the numbers and every task shows the wrong phase without any error. Number the phases in the same order as the values in the formula.

Variations

=IFERROR(CHOOSE(B2, "Planning", "Interim audit", "Year-end audit", "Reporting"), "Unknown phase")

=IFERROR(CHOOSE(B2; "Planning"; "Interim audit"; "Year-end audit"; "Reporting"); "Unknown phase")

Why it works

MONTH turns any date into a position from 1 to 12, and the list in the formula is ordered by month, so the position is exactly the month number. The fiscal year starts in April, which is why a list works where arithmetic on the month would need a correction.

More to watch out for

• List only eleven quarters and December gives #VALUE!, but only for December invoices, so the mistake can hide until year-end. Count the values: twelve.

Variations

=IF(B2 = "", "", CHOOSE(MONTH(B2), "Q4", "Q4", "Q4", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3"))

=IF(B2 = ""; ""; CHOOSE(MONTH(B2); "Q4"; "Q4"; "Q4"; "Q1"; "Q1"; "Q1"; "Q2"; "Q2"; "Q2"; "Q3"; "Q3"; "Q3"))

Why it works

CHOOSE can return a reference, so what comes out of it is a live range that SUM can work on. ROUNDUP turns the month into a quarter number because a part of a quarter belongs to the whole quarter, and it never produces 0 for months 1 to 12.

Watch out for

• Use INT instead of ROUNDUP and most months land in the wrong quarter: INT(8 / 3) is 2, so August is totaled as Q2. Months 1 and 2 give position 0 and #VALUE!.

  ROUND fails too: INT and ROUND both break on a month such as 7 or 10. Always test with the first and the middle month of a quarter.

• Month 0 or month 13 gives position 0 or 5, and CHOOSE returns #VALUE!. Limit the input with data validation to whole numbers from 1 to 12.

Variations

=SUM(INDEX(B2:E5, 0, ROUNDUP(H1 / 3, 0)))

=SUM(INDEX(B2:E5; 0; ROUNDUP(H1 / 3; 0)))

Why it works

CHOOSE cuts a fractional index off instead of rounding it, so a continuous value such as 4.4667 falls into exactly one bucket. LET names the day count and the position, so the final line reads like the aging rule.

Do not use this construction when the bands are uneven (0, 30, 60, 120, 365) or maintained by the client: put the bands in a table and use XLOOKUP with match_mode -1, as in the XLOOKUP page, so the band edges are visible cells.

More to watch out for

• Without MAX(1, ...), an invoice due 60 days from now gives -61 / 30 + 2 = -0.0333, CHOOSE cuts that to 0 and the cell shows #VALUE!. The error appears only when such an invoice exists.
• Use TODAY() instead of a fixed balance sheet date and the buckets change every day. For a year-end analysis, type the balance sheet date in a cell.

Variations

=LET(
  days, $F$1 - B2,
  position, MIN(5, MAX(1, INT((days - 1) / 30) + 2)),
  CHOOSE(position, "Not yet due", "1 to 30 days", "31 to 60 days", "61 to 90 days", "Over 90 days")
)

=LET(
  days; $F$1 - B2;
  position; MIN(5; MAX(1; INT((days - 1) / 30) + 2));
  CHOOSE(position; "Not yet due"; "1 to 30 days"; "31 to 60 days"; "61 to 90 days"; "Over 90 days")
)