+ Reply to Thread
Results 1 to 7 of 7

Super Advanced OFFSET formula, almost have it

  1. #1
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Super Advanced OFFSET formula, almost have it

    "Goal: I am trying to write a formula for cell J13 that returns the money value by looking up all the rest of information from the inputs in column J4-J6. For clarity, I want a formula in J13 that takes the 3 values above it to find the $$$ in column D by reference. So J6 = 1000 leading to the top chart section, J4 = A leading to the first 3 rows, J5 = Average leading to D7 which is $50. Or 3000 B Poor leading me to $15 or 5000 A Fair leading to $80. I have been able to get close using if multiple times, but I would like it to run smoothly for the rest of the charts, which add rows and are different sizes than the first chart, If someone could change my tweeking formula in J13, I'd Appreciate it. Thanks
    "
    Attached Files Attached Files
    Last edited by A Ru; 12-19-2016 at 06:57 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Super Advanced OFFSET formula, almost have it

    If you lose all of the merged cells and arrange the data in database fashion, it's straightforward:

    B
    C
    D
    E
    F
    G
    H
    4
    Qual
    Cost
    Cond
    ???
    A
    5
    A
    1000
    Good
    70
    Average
    6
    A
    1000
    Average
    50
    2000
    7
    A
    1000
    Fair
    35
    8
    B
    1000
    Good
    65
    Result
    $ 50.00
    G8: {=INDEX($E$4:$E$38, MATCH(1, ($B$4:$B$38=G4)*($C$4:$C$38=COST_H12)*($D$4:$D$38=Construction_Quality), 0))}
    9
    B
    1000
    Average
    45
    10
    B
    1000
    Fair
    30
    11
    A
    2000
    Excellent
    105
    12
    A
    2000
    Good
    70
    13
    A
    2000
    Average
    50
    14
    A
    2000
    Fair
    35
    15
    B
    2000
    Excellent
    90
    16
    B
    2000
    Good
    65
    17
    B
    2000
    Average
    45
    18
    B
    2000
    Fair
    30
    19
    A
    3000
    Good
    55
    20
    A
    3000
    Average
    35
    21
    A
    3000
    Fair
    25
    22
    A
    3000
    Poor
    20
    23
    B
    3000
    Good
    50
    24
    B
    3000
    Average
    35
    25
    B
    3000
    Fair
    25
    26
    B
    3000
    Poor
    15
    27
    A
    4000
    Good
    70
    28
    A
    4000
    Average
    50
    29
    A
    4000
    Fair
    35
    30
    B
    4000
    Good
    60
    31
    B
    4000
    Average
    45
    32
    B
    4000
    Fair
    30
    33
    A
    5000
    Good
    150
    34
    A
    5000
    Average
    115
    35
    A
    5000
    Fair
    80
    36
    B
    5000
    Good
    140
    37
    B
    5000
    Average
    100
    38
    B
    5000
    Fair
    75
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: Super Advanced OFFSET formula, almost have it

    I had something similar to this originally, before I changed to merged cells. Could you possibly provide an answer with keeping the original formattings?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Super Advanced OFFSET formula, almost have it

    I have no suggestion for that. It's terrible practice in Excel.

  5. #5
    Registered User
    Join Date
    07-08-2016
    Location
    USA
    MS-Off Ver
    Office 365, Office 2016 for PC & Mac, Office 2013, Office 2011 Mac
    Posts
    80

    Re: Super Advanced OFFSET formula, almost have it

    Hi A Ru,

    This works with the formatting. The only thing I changed was modifying "Average " because it had a trailing space in the tables. If you don't want to change your data validation just throw a wild card "?" after 'construction_quality' named range (construction_quality&"?"). You can simplify this formula by slightly changing the layout. In the attached workbook, I threw in a couple formatting suggestions that wouldn't greatly change the layout, but would greatly help with simplifying the formula.

    Please Login or Register  to view this content.
    DMG
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-19-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    20

    Re: Super Advanced OFFSET formula, almost have it

    First of all thank you so much, this is exactly what I was looking for and I like the idea of the revised formula, unfortunately I cannot changed the actual format because of too many other formulas stemming from the actual info. Can you please see the current excel as your formula worked perfectly for what I asked, but there is a slight tweek in my actual working excel. The 1000,2000,3000, etc... but in actually it refers to a number and a page, could you possibly revise to accommodate this? I have tried tweeking it myself, but keep messing up. Thank you!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-08-2016
    Location
    USA
    MS-Off Ver
    Office 365, Office 2016 for PC & Mac, Office 2013, Office 2011 Mac
    Posts
    80

    Re: Super Advanced OFFSET formula, almost have it

    cost_h12/1000--->match(cost_h12,s4:s8,0)

    This changes your choose index number.

    Please Login or Register  to view this content.
    DMG
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. New Member Seeking help; Am a Seasoned Excel User But Not Super Advanced
    By bwcarroll855 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-12-2015, 12:32 PM
  2. Super lookup formula help needed!
    By yayayken in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-08-2015, 04:03 PM
  3. [SOLVED] Super Easy Make Multiple Cells use *1.2 formula
    By flextera in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-06-2015, 02:53 PM
  4. [SOLVED] Super cool named ranges with offset.
    By Polymorpher in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-02-2014, 09:11 AM
  5. Advanced (AI) Chart, OFFSET, INDEX Formula Help
    By dluhut in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-14-2013, 11:52 AM
  6. Excel 2007 : IF Super formula
    By Swamp Stomper in forum Excel General
    Replies: 6
    Last Post: 07-30-2012, 07:32 AM
  7. Replies: 0
    Last Post: 09-16-2010, 10:44 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1