Results 1 to 2 of 2

Thread: Referencing multiple values to determine an equation

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    34
    Articles
    0
    Excel Version
    365

    Referencing multiple values to determine an equation

    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

Posting Permissions

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