This a budget spreadsheet laid out in 13 weeks. In cell A4 is the Budget per week (28,000$). In cells B4 to N4 (13 cells for 13 weeks) are where entries are made on how much was spent.
In cell P4, Q4, and R4 I have monthly budget remaining totals, which takes the budget * number of weeks in the month - amount spent in those weeks.
P4 is =A4*4-SUM(B4:E4)
Q4 is =A4*4-SUM(F4:I4)
R4 is =A4*5-SUM(J4:N4)
What I want cell O4 (current month remaining budget) to do is when entries are only in cells for the first month(B4,C4,D4,E4), to show cell P4's value. When entries are in the 2nd month(F4,G4,H4,I4), to use Q4's value. When entries in the 3rd month(J4,K4,L4,M4,N4), to use R4's value.
I've tried numerous variations with IF and AND to no avail. Any ideas? Thanks
Bookmarks