I have an income statement projection sheet and I attached one part of it below.
I have a question for the yellow line (Row 7). I try to find a formula to calculate the tax based on Row 5. The logic is: for any cell (cell X7) between H7 to R7, if the sum value of cells before X7 is negative or 0 (sum(H7:X7)<=0), then X7=0. It means that if pretax income is negative or 0 up to the current year, then we need to pay no tax.
Row 6 is the sum of H7 to X7. (H6=SUM($H5:H5), I6=SUM($H5:I5), etc...).If sum(H7:X7)>0 then we have two situations. If X is the first year Row 6 value turns positive, the X7 = X6*tax rate 30%, if it's not the first year then X7=X5*tax rate 30%. For example, H6, I6, J6 are all negative, K6 is the first year the sum turns positive, so K7 should be K6*30%=3978 instead of 5353, the number in the pic is wrong since I didn't find a valid formula to do this. The logic is that if I lost money in prior years, the money I lost can reduce the tax base in the first year that I made money so i don't need to pay so much tax.
The problem is, this model is used for all kinds of projects, one can have the first positive sum in year 3, the other one can have the first positive sum in year 4. So my formula needs to automatically identify the first positive sum and treat it differently. I have a feeling that countif + index can do it but I can't think of a valid formula to make it work.
