Jan 14 2022 09:23 PM - edited Jan 14 2022 10:14 PM
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)
Jan 14 2022 10:33 PM
@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?
Jan 14 2022 10:50 PM
@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.