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

Can we Use (if,or,and) at a time

S

satya111

New Member
Hi ,


Can we Use if,or,and at a time


basically i want to check Turn around Time For Team ,


But In a team there are three Process running


1-team has 24hrs TAT

2-team has 48hrs TAT

3-team has 82hrs TAT


i can take it differently for each team but is there any chance we can do in one shot


regards

Satya
 
Hi Satya ,


Can you please be more specific ?


When you say "I want to check TAT for a team" , what does this mean ? Please note that this forum is devoted to using Excel to solve problems ; there will be those who are knowledgeable in Excel , those who have domain knowledge , and those who combine the two types of knowledge.


If you want that those who do not have domain knowledge also try and help out , then you have to give more details about your situation , what you want or what problems you are facing in your attempts at a solution.


If I have to be very general , then certainly you can combine IF , OR and AND in one formula :


=IF(OR(AND(C15>0,C15<=27),D15="1102"),17,"")


is a perfectly valid IF formula.


Narayan
 
Sorry.


Note:(TAT means TURN Around TIME)


My actually Question is (I have to do Turn Around Time For A different teams . How can i do in one Time )


For Better understanding i am giving an a example


Example:

A B C c-b

Team In-time Out-time Duration statusoftat

Kar 12:55 16:55 4:00 is in Turn Around Time

up 10:00 17:55 7:55 is Not in Turn Around Time

AP 11:00 18:55 7:55 is in Turn Around Time


as the above there are three teams ..for each team there is the set of (Turn Around Time)


if Kar team duration anything is >=4hrs is in Turn Around Time &

if Kar team duration anything is <4hrs is Not in Turn Around Time


in the same way each team has different Turn Around Time Lines .


so i have data same as the above example ..


i want to find out any better way to do it


if anything wrong in my Mail Please let Me know ..i will correct it


regards

satya
 
Hi Satya ,


Thanks for the details. I am still confused , sorry.


I understand that you have several teams , examples being "Kar" , "up , "AP".


You have data on :


1. Team Name , in column A

2. In Time , in column B

3. Out Time , in column C


The duration , in column D , can be calculated , as the difference between the times in column C and those in column B.


The TAT is still not clear ; you say that each team has different TAT time lines ; does this mean that the In Time and Out Time for each team is different ?


If we assume this is so , then the example you have given , where the duration is 7:55 for team "up" , and they are not in TAT , while the same duration , for team "AP" is in TAT , is understood.


So what is it that you wish Excel to do ? Calculate the Duration , and the Boolean value , either within TAT or outside TAT based on the Duration , and the defined TAT time lines for each team ?


If you can upload a sample worksheet or post some sample data , the formulae can be worked out.


Narayan
 
i have gone throw how to upload spreadsheet


PFL of GOOGLE.DOCS


https://docs.google.com/spreadsheet/ccc?key=0AuXuCJrV1lCvdEpiX201RHdick1STzJmdkhTQUhWaUE#gid=0


Satya
 
Hi Satya ,


Can you please provide everyone access to the worksheet ?


I have sent you an access request , but it is better if you provide everyone access to it.


Narayan
 
=IF(INDEX($A$3:$B$5,MATCH(A11,$A$3:$A$5,0),2)>D11,"less time",IF(INDEX($A$3:$B$5,MATCH(A11,$A$3:$A$5,0),2)<D11,"More time","Same time"))


May be this should work, change your requirement as applicable in between the " ".
 
Hi Satya ,


Thanks for uploading the sample worksheet. I assume that if the values of Duration , in column D , are greater than the TAT times given in the table A3:B5 , then the TAT is not met.


The following formula should do the job :


=IF(INDEX($B$3:$B$5,MATCH(UPPER(TRIM(A11)),UPPER(TRIM($A$3:$A$5)),0))≥D11,"Met","Not Met")


Put this in E11 as an array formula ( using CTRL SHIFT ENTER ) , and copy it downwards.


Narayan
 
Hi Narayan,


with the First Formula it dint work but it worked with second one


if it is working with first one can u share me the xlx which u worked on


to satyakalyan122@gmail.com


thanks a lot for helping


1.=IF(INDEX($B$3:$B$5,MATCH(UPPER(TRIM(A11)),UPPER(TRIM($A$3:$A$5)),0))≥D11,"Met","Not Met")


2.=IF(INDEX($A$3:$B$5,MATCH(A11,$A$3:$A$5,0),2)>D11,"less time",IF(INDEX($A$3:$B$5,MATCH(A11,$A$3:$A$5,0),2)<D11,"More time","Same time"))


satya
 
You must log in or register to reply here.
Back
Top