jski21
Board Regular
- Joined
- Jan 2, 2019
- Messages
- 133
- Office Version
- 2016
- Platform
- Windows
Good afternoon Mr. Excel Team,
Trying to have a budget line item show up under the appropriate month only if that month is less than the current date and if true, calculate the monthly budget amount. Using this formula: =IF(B8<$A$3,$N$10/12,""). Right now the formula should show only the months that are prior to today (October 2021), yes? However, carrying the formula across the range, all the cells populate with the calculated monthly budgeted amount. BTW, using a fiscal year format for what it's worth.
B8 = Date of the appropriate month
$A$3 = Today's date
$N$10/12 = the annual budget amount divided into monthly increments
Here's the spreadsheet:
Thanks in advance for the look/see and assistance.
jski
Trying to have a budget line item show up under the appropriate month only if that month is less than the current date and if true, calculate the monthly budget amount. Using this formula: =IF(B8<$A$3,$N$10/12,""). Right now the formula should show only the months that are prior to today (October 2021), yes? However, carrying the formula across the range, all the cells populate with the calculated monthly budgeted amount. BTW, using a fiscal year format for what it's worth.
B8 = Date of the appropriate month
$A$3 = Today's date
$N$10/12 = the annual budget amount divided into monthly increments
Here's the spreadsheet:
Operating Budget2.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
3 | As of October 2021 | |||||||||||||||
4 | ||||||||||||||||
5 | ||||||||||||||||
6 | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | |||
7 | Total | |||||||||||||||
8 | ACCOUNT NAME | 06/30/21 | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Annual | ||
9 | General Administration | |||||||||||||||
10 | Monthly Expense | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 361,970 | 4,343,636 | ||
SalCap (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3 | A3 | ="As of "&TEXT(TODAY(),"mmmm yyyy") |
B8 | B8 | =EOMONTH($C$2,5) |
C8 | C8 | =EOMONTH($C$2,6) |
D8 | D8 | =EOMONTH($C$2,7) |
E8 | E8 | =EOMONTH($C$2,8) |
F8 | F8 | =EOMONTH($C$2,9) |
G8 | G8 | =EOMONTH($C$2,10) |
H8 | H8 | =EOMONTH($C$2,11) |
I8 | I8 | =EOMONTH($C$2,12) |
J8 | J8 | =EOMONTH($C$2,13) |
K8 | K8 | =EOMONTH($C$2,14) |
L8 | L8 | =EOMONTH($C$2,15) |
M8 | M8 | =EOMONTH($C$2,16) |
B10:M10 | B10 | =IF(B8<$A$3,$N$10/12,"") |
N10 | N10 | =21718180*0.2 |
Thanks in advance for the look/see and assistance.
jski