IF Function spilling

Question

I've created a complex IF Function with nesting in order to give me an accurate estimation of my monthly and yearly income/ expenses based on previous fortnightly bookkeeping. I've gotten up to one IF formula and it's returning multiple values when i'm only looking for 1 answer to the formula.

-Excel 365

The formula for February expenses is:

=IF(C2=44605,(((SUM(C34:C37))/14)*C3)+(SUM(G30:G33))-((SUM(G30:G33))/14),IF(C2=44619,(((SUM(C34:C37))/14)*C3)+(SUM(G30:H33))-((SUM(G30:G33))/14),IF(AND(C2>=44562,C2<=44592),((C49/31)*28),(SUM(G30:H33))-((SUM(G30:G33))/14)+((I30:I33)/14))))

Where C2 is 31/12/22, C3 is how many days are left in the month, and each of the numbers (i.e 44605 is the number value of a date - 13/2/22)

riny_van_eekelen · Answer

OliverHoldway&nbsp;And how does that formula look like?

oliverholdway · Answer

Riny_van_Eekelen I've re-loaded this for you

riny_van_eekelen · Answer

OliverHoldway&nbsp;Testing your formula but it seems overly complicated. Am stuck on one bit. What exactly do you have in C3. You mention that it contains "how many days are left in the month". What value/formula is in there now?

riny_van_eekelen · Answer

OliverHoldway&nbsp;Got it now! Its the very last part of the formula:&nbsp;+((I30:I33)/14)&nbsp;Here, you add a range of cells divided by 14. Excel then spills the result for each of the cells in an array of 4 cells. I guess you want to use +SUM(I30:I33)/14 here.

Forum Discussion

IF Function spilling

Resources