# Thread: Using result of drop down list to perform multiplication, based on table of values

1. Registered User
Join Date
Apr 2010
Posts
1

## Unanswered: Using result of drop down list to perform multiplication, based on table of values

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.

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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:
Code:
`=\$A2*INDEX(Rates,MATCH(\$B2,FrequencyLookUp,0),MATCH(C\$1,ExpenseLookUp,0))`
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...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•