Forum Discussion
Solved
IF 2 cells meet criteria
Hi there,
In the below chart (text is in column A, $$ in column C). I need a formula in Ending cash balance
Current formula is =IF(C8<1,C4+C6-C7-C9,C4+C6-C8-C9). So if there is nothing in actual expenses then it pulls the budgeted expenses and if there is anything in actual expenses it pulls only the actual expenses. But now I've added in the budgeted revenue and I want it to add in the budgeted revenue only if there is nothing in the actual revenue. How can I change this formula to do that? So in the below example it should show ending balance as $101,500.
| A | B | |
| 4 | Starting Cash Balance | $ 100,000.00 |
| 5 | Total Budgeted Revenue | $ 5,000.00 |
| 6 | Total Actual Revenue | $ - |
| 7 | Total Certainli Only Expenses Budgeted | $ 2,000.00 |
| 8 | Total Certainli Only Expenses Actual | $ 2,500.00 |
| 9 | Total Paid to CRDCI | $ 1,000.00 |
| 10 | Ending Cash Balance | $ 96,500.00 |
- Perfect, thank you so much!!
- When there is actual expenses it works, but when there is only budgeted it doesn't count the budgeted revenue. See below it gives 97000 as ending cash balance but in this case it should be accounting for the 5000 budgeted revenue and therefor should result in 102000 not 97000
Starting Cash Balance $100,000.00
Total Budgeted Revenue $5,000.00
Total Actual Revenue $-
Total Certainli Only Expenses Budgeted $2,000.00
Total Certainli Only Expenses Actual
Total Paid to CRDCI $1,000.00
Ending Cash Balance $97,000.00 You can modify your formula to include the budgeted revenue if both the actual revenue and certain expenses are blank. Here is the updated formula:
=IF(AND(C6="",C8<>"",C9<>""),C4+C5-C7-C9,IF(C8<>"",C4+C5-C8-C9,IF(C8="",C4+C6-C7-C9,"")))
This formula checks three conditions:
- If the actual revenue (C6) is blank, the budgeted revenue (C5) is considered.
- If the actual expenses (C8) are not blank, it subtracts them from the calculation.
- If the certain expenses (C9) are not blank, it subtracts them from the calculation.
Copy this formula to your "Ending Cash Balance" cell (assuming it's in C10 in your example), and it should give you the desired result. This formula first checks for actual revenue, and if it's blank, it considers the budgeted revenue. Then, it adjusts the calculation based on the presence of actual and certain expenses. The text was revised with the AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
