IF function help

Jai Sharp

New Member
Joined
Mar 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I'm pretty sure I'm needed to use the IF function, but to be honest I'm really not sure as I have been looking for answers on this all day and can't figure it out. I have made an example spreadsheet using Google Sheets and I have linked that below so you can go in and see exactly what I'm wanting to do.

LINK to Spreadsheet:
zTest - Personal Expenses

What I want to do...
On the Expense tab I would like to be able to select they billing period from the Billing column and then from that option calculate the monthly and yearly cost for each item added to the expense tracker.

I have the billing durations set in the List’s tab:
Yearly - 1
Quarterly - 4
Monthly - 12
Bi-Weekly - 26
Weekly - 52
Daily - 365
Work Weeks - 50
Work Days - 250

And then I have an example on the “Test Sheet” of how I would want the formulas to preform. Basically I would like it to be a selectable formal like this.

(Amount) x (Perior) = Yearly Amount
(Yearly Amount) / 12 = Monthly Amount

Any help would be super awesome, extra points if you just want to write the formal for me :)

Cheers
Jeremy
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jai Sharp

New Member
Joined
Mar 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
SOLVED :)
This has been solved by using a VLOOKUP formula, I'll leave the Google Sheet up for a little bit for people to check out if they want. Thanks to everyone who helped out I really appreciate it.
 
Solution

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,409
@Jai Sharp - Welcome to the MrExcel Message Board!
And thanks for sharing the solution.

Leaving the Google Sheet up for a while wouldn't actually help any future reader considering Excel users are still finding solutions in 20 years old questions on the board, and Internet users don't like to download public files due to security concerns. That's why we try to use the XL2BB tool to create the mini-sheets on the MrExcel Message Board instead of giving links to the actual files that are going to be dead-links in a short time. Using the XL2BB also helps to transfer the existing data and formulas easily and reduces the response times dramatically that also encourages the helpers to solve the questions instead of trying to create the entire structure in their workbooks manually.

Although I will confirm your own marked solution for this question, I simply created mini-sheets from your workbook and pasted them below for future readers. That would be great if you could also consider doing the same to help the others next time - of course, if it is also convenient for you.
Book1
BCDEFGHIJ
4Expense Tracker
5ExpenseAmount of Bill
6Name, Due Date and Payment MethodBillingAmountMonthly CostYearly Cost
7Expense NameDue DateCard
8Disney PlusNov_28VisaMonthly$79.9912$79.99$959.88
9Netflix1st of MonthMaster CardQuarterly$14.994$5.00$59.96
10Car Insurance14th of MonthChecking AccountWeekly$14.0052$60.67$728.00
11House Insurance4th of monthChecking AccountDaily$200.00365$6,083.33$73,000.00
12$0.00$0.00
13$0.00$0.00
14$0.00$0.00
15$0.00$0.00
16$0.00$0.00
17$0.00$0.00
18$0.00$0.00
19$0.00$0.00
20$0.00$0.00
21$0.00$0.00
22
23Total Amount $0.00$6,228.99$74,747.84
Expenses
Cell Formulas
RangeFormula
I8:I21I8=J8/12
J8:J21J8=H8*G8
H8:H11H8=VLOOKUP(F8,Lists!$B$2:$C$10,2,FALSE)
F23,I23:J23F23=SUM(F7:F22)

Book1
BC
2PeriodAnnual
3Yearly1
4Quarterly4
5Monthly12
6Bi-Weekly26
7Weekly52
8Daily365
9Work Weeks50
10Work Days250
Lists
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top