Results 1 to 2 of 2

Thread: Referencing multiple values to determine an equation

  1. #1
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    23
    Articles
    0
    Excel Version
    xlsm

    Referencing multiple values to determine an equation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have four tables that I'm trying to reference data from and compare it to manually input data in another area of my spreadsheet to determine what factor should be used in a specific line equation.

    The tables all have the same ranges and are setup like this; where A% equals 1%, 2%, 3% or 5%, referencing one of the 4 tables.
    A%

    Limit (Expressed in $)
    Ded. Up to 59,999 60,000 to 99,999 100,000 to 200,000 200,001 & Over
    500 0.98 0.98 0.97 0.97
    750 - 0.95 0.94 0.94
    1000 - - 0.86 0.86
    1500 - - 0.82 0.82
    2500 - - - 0.74
    5000 - - - 0.65
    7500 - - - 0.61
    10000 - - - 0.59
    12500 - - - 0.56
    15000 - - - 0.53
    25000 - - - 0.49
    50000 - - - 0.41
    The values in the table change, however, the first column doesn't.

    I'm trying to avoid writing a very lengthy IF statement if possible. Basically I need a formula that says:
    IF E25 equals A% reference table X AND match E24 to column 1 AND based off of value D2, determine which column's factor should be referenced to determine the final equation that should be run.

    If that is confusing, below is a copy of the IF statement I started to write which might give a better idea as to what I'm trying to do:
    IF(AND(E25=1%,D2<60000,E24=500),D24*.98,etc.

    Any help is appreciated!
    Last edited by Joecam; 2014-05-28 at 03:04 PM. Reason: Table clarification

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming your table is in Shee1, row headers starting at A3 (i.e. 500 is in A3, 750 is in A4, etc) and in B2 across you enter only the lower bounds of your ranges (i.e. 0, 60000, 100000, 200001)

    Then assuming, your input information is in another sheet, try this formula:

    =D24*INDEX(Sheet1!$B$3:$E$14,MATCH(E24,Sheet1!$A$3:$A$14,0),MATCH(D2,Sheet1!$B$2:$E$2))


Posting Permissions

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