I have a model in which I can set a particular Category to either 1 or 2 (cell C2).

I have a series of numbers aligned to either Category 1 or Category 2 (using, for example, IF(B6=1,H6,H7) ) and I want to be able to find the difference between values in each category. I use a self-referring IF statement to keep the category 2 value “locked” on the spreadsheet (for example, the formula in cell D6 is IF(B6=2,C6,D6)). I then want to be to be able to switch back to category 1 (by adjusting the value in cell C2), and have the spreadsheet show the difference between the two (the formula in cell E6 is simply D6-C6).

However, the difference always shows as zero. I check the Evaluate Formula function and it somehow steps through to be 30-5=0. If I hard-code the D6 value, the E6 subtraction works correctly. I really want to avoid this, as it will make downstream maintenance of the model difficult.

I think it’s something to do with the self-referring IF, but I don’t understand why, or how to fix it.
Image 1 is the spreadsheet showing the formulas I’ve used; image 2 is the same spreadsheet showing the results.

This is Excel as part of M365 for Enterprise, version 2404. VBA is out, due to organisational security restrictions. Any help gratefully received. Thankyou.