• 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...

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

Nested if

R

REENA GUPTA

Member
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
 

Attachments

  • CALL REPORT CHANDOO FORUM.xlsx
    358.5 KB · Views: 4
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.
 

Attachments

  • Copy of CALL REPORT CHANDOO FORUM.xlsx
    338.4 KB · Views: 1
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
 

Attachments

  • CALL REPORT CHANDOO FORUM.xlsx
    358.3 KB · Views: 3
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.
 

Attachments

  • CALL REPORT CHANDOO FORUM.xlsx
    376.4 KB · Views: 3
Last edited:
GraH - Guido said:
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.
Click to expand...


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
 
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 ?
 
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.
 

Attachments

  • Copy of CALL REPORT CHANDOO FORUM2.xlsx
    344.8 KB · Views: 1
GraH - Guido said:
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.
Click to expand...

Hi
But it is working ok now . Earlier it was not working correctly.
 
You must log in or register to reply here.
Back
Top