 New Contributor

# IF Function spilling

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)  4 Replies

# Re: IF Function spilling

@OliverHoldway And how does that formula look like?

# Re: IF Function spilling

@Riny_van_Eekelen I've re-loaded this for you

# Re: IF Function spilling

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

# Re: IF Function spilling

@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.