Hi there - I am working at building a budget spreadsheet. Some of my expenses are daily, some are weekly, some monthly etc. As a result, I have created a named list: "Frequency" which has each of the periodic frequencies listed by name. I used this list to create a drop down via Data Validation, so now I can specify whether an expense value is a daily rate, a monthly rate etc.

When inputting data in to the spreadsheet, one enters an expense amount (say $10) and then specifies whether this is a daily, weekly, monthly amount by choosing the appropriate Frequency measure from the list.

The spreadsheet then has a Monthly expense and Annual expense column which I would like to be formula driven - they should calculate the Monthly and Annual rates of these expenses which may have been input as daily, weekly, quarterly rates.

Depending on which frequency is chosen, I want the total to be multiplied by the value required to make these expenses a Monthly amount or an Annual amount.

I have a table which shows the rates that need to be multiplied for Monthly or Annual totals:

Monthly Rate Annual Rate
Daily 30.42 365
Weekly 4.33 52
Fortnightly 2.17 26
Monthly 1.00 12
Bi-Monthly 0.50 6
Quarterly 0.33 4
Semi-Annually 0.17 2
Annually 0.08 1

My question is:

How do I get a result to be generated by building a formula, such that where the expense is input as $10 and from the Frequency drop down, the frequency weekly is selected, which performs the function of multiplying $10 by 4.33.

Thanks in advance for your help - if I haven't been clear, please let me know and I'll try again.

Hi,

You can look up the conversion rate in your table.

I've attached an example to help. I used the named range 'Frequencies' rather than 'Frequency' because there is already an Excel worksheet function called FREQUENCY so I wanted to avoid any confusion.

Note that the headers in both tables are the same to make the look up easier. The look up formula in C2 is:
If a Frequency hasn't been selected then the formula would return #N/A. To address this you could use IFERROR() or ISERROR() functions, but I have simply added an extra row to the lookup table with a frequency of 0.

If you want me to go through any of it in more detail then please let me know...

Hope that helps...