1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested if

Discussion in 'Ask an Excel Question' started by REENA GUPTA, Jun 12, 2018.

  1. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Hi

    I have to calculate "due by time" . Due by time is different based on the combination of priority(HIGH/MEDIUM/NORMAL) & Region (CO/NR/WR/ER) . Conditional matrix is shown in the attached sheet . I am using office 2007. I am totally confused . Please help me how to write this nested if condition .. .
    kindly advise me if there is any other way to calculate due by time.

    Thanks in advance

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    320
    In K3 =IF(J3="High", VLOOKUP(C3,$U$5:$X$8,2,0),IF(J3="Medium",VLOOKUP(C3,$U$5:$X$9,3,0),VLOOKUP(C3,$U$5:$X$8,4,0))) and copy down.
    Thomas Kuriakose and REENA GUPTA like this.
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    In this case I would use index-match-match like so
    K3: =INDEX($V$5:$X$8,MATCH($C3,$U$5:$U$8,0),MATCH($J3,$V$4:$X$4,0))
    and drag down.

    Attached Files:

  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,853
    Or.......

    K3, copied down :

    =VLOOKUP(C3,U$5:X$8,MATCH(J3,U$4:X$4,0),0)

    Regards
    Bosco
  5. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Thanks ! it worked
  6. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Thanks ! it worked well
  7. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Thanks ! it worked well
  8. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Now I want to find the delay (In hours)by calculating the difference in col K & col I . Please help me to calculate delay in hours & minute. I tried my best .
    Regards

    Attached Files:

  9. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    your column I is formatted as text (because of the used formula) and column K is a value representing time (hours).
    So I updated your lookup area to show "time".
    Then you can do a simple minus.
    Because time cannot be negative, I wrapped it in an IF.
    See attached if suitable.

    Attached Files:

    Last edited: Jun 12, 2018
  10. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21

    Hi Grah

    Thanks a lot for the trick. Actually I was also doing the same i.e. trying to update the lookup area as time by formatting it as hh:mm but in case of 24 hrs & 48 hrs it was showing 0:0 which I failed to sort out. You had used the square bracket for hrs i.e.[h]:mm. That is where I was wrong.

    Thanks a lot for the help
    Regards
  11. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Hi
    Please advise if I want to calculate the same due by time without lookup table in the attached sheet. In that case what would be the formula in excel 2007. Nested Ifs ?
  12. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    General advise is to replace nested ifs by (v)lookup if possible. So why would you want to go in the opposite direction?
    Now you can go without the separate table. Highlight the reference to that table in the formula and press F9. It will be replaced by a hard coded string of values representing a table. Then press enter. You can then delete the separate table.

    Note: I had to change your formula "[h]", because of my local settings I need to use . To make it work, just change it back to your local settings.

    Attached Files:

  13. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21
    Hi
    But it is working ok now . Earlier it was not working correctly.
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Thomas Kuriakose likes this.
  15. REENA GUPTA

    REENA GUPTA New Member

    Messages:
    21

Share This Page