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 GUPTAMember

Messages:
36
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.

File size:
358.5 KB
Views:
4
2. AlanSidmanActive Member

Messages:
423
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 - GuidoWell-Known Member

Messages:
908
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.

File size:
338.4 KB
Views:
1
4. bosco_yipExcel Ninja

Messages:
2,113
Or.......

K3, copied down :

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

Regards
Bosco
5. REENA GUPTAMember

Messages:
36
Thanks ! it worked
6. REENA GUPTAMember

Messages:
36
Thanks ! it worked well
7. REENA GUPTAMember

Messages:
36
Thanks ! it worked well
8. REENA GUPTAMember

Messages:
36
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

File size:
358.3 KB
Views:
3
9. GraH - GuidoWell-Known Member

Messages:
908
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:

• CALL REPORT CHANDOO FORUM.xlsx
File size:
376.4 KB
Views:
3
Last edited: Jun 12, 2018
10. REENA GUPTAMember

Messages:
36

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 GUPTAMember

Messages:
36
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 - GuidoWell-Known Member

Messages:
908
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.

File size:
344.8 KB
Views:
1
13. REENA GUPTAMember

Messages:
36
Hi
But it is working ok now . Earlier it was not working correctly.
14. NARAYANK991Excel Ninja

Messages:
16,619
Thomas Kuriakose likes this.

Messages:
36