Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Question 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. #2
    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...
    Attached Files Attached Files

Posting Permissions

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