IF Function spilling

Copper Contributor

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)

 

Screen Shot 2022-01-15 at 5.13.40 pm.png

 

Screen Shot 2022-01-15 at 4.58.58 pm.png

4 Replies

@OliverHoldway And how does that formula look like?

@Riny_van_Eekelen I've re-loaded this for you

@OliverHoldway 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?

@OliverHoldway Got it now! Its the very last part of the formula:

 

+((I30:I33)/14)

 

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.