excelexcel-formula

Get value of the 4th previous quarter of a given value (quarter)

I have a given string "2023-1" which represents the first quarter of 2023. In Excel I have a formula:

=IF(AND($A2 => [value to calculate];$A2 <= "2023-1");$B2;0)

Now [value to calculate] should have the value "2022-2" (always the 4th previous quarter of the seconde value in the formula (in this case 2023-1).

More examples:

and so on.

I don't want to use VBA, just regular formulas in Excel. What would be a way to do this?

I already experimented using TEXTBEFORE and TEXTAFTER and subtract 4 of the second part of the string. But then I still have to do some adjustments i.e. 4-4 becomes 0 but is should be 1 (first quarter). 3-4 = -1 but should be 4th quarter but of the previous year.

Are there better solutions to solve this without a few nested IF-statements?

Solution

  • You can try this:
    Note that the current quarter is hard-coded, but you could make it a cell-reference instead:

    =LET(
    cq, "2023-1",
    cy, TEXTBEFORE(cq, "-"),
    cm, (TEXTAFTER(cq, "-") - 1) * 3 + 1,
    cd, DATE(cy, cm, 1),
    pd, EDATE(cd, -9),
    pq, YEAR(pd) & "-" & INT((MONTH(pd) - 1) / 3) + 1,
    res, IF(AND($A2 >= pq, $A2 <= cq), $B2, 0),
    res
)

    Algorithm