Yearly and quarterly bills per month
PremiumIn D2, turn the amount in B2 into a monthly amount: divide yearly bills by 12 and quarterly bills by 3, and keep monthly bills as they are. Put one IF inside another.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Bill | Amount | Paid | Per month | ||
| 2 | Car insurance | 4800 | Yearly | |||
| 3 | Rent | 3000 | Monthly | |||
| 4 | Water | 900 | Quarterly | |||
| 5 | Phone | 250 | Monthly | |||
| 6 | Gym | 3000 | Yearly | |||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Budget and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
The first IF asks whether the bill is paid yearly. If it is, the amount is divided by 12. If not, the second IF asks whether it is quarterly and divides by 3, and anything else is already a monthly amount. 4,800 a year for car insurance is 400 a month. Fill the formula down, and every bill gets its monthly share.
Common mistake: Leaving the quotes off the text, C2=Yearly. Excel then reads Yearly as a name and shows #NAME?.
The answer
- D2
=IF(1C2="Yearly",2B2/12,3IF(C2="Quarterly",B2/3,B2))
In plain words: 1If the bill in C2 is paid yearly, 2divide the amount in B2 by 12. 3Otherwise a second IF divides quarterly bills by 3 and keeps the rest as they are.