Formula

[Johnstog]

Probably an easy one for most of you. I need an IF statement.

In column W is where the formula would go.

IF a there is a time of 31-sec or more in Column U, then an "A" should be entered in Column W.
IF there is a time of 31-sec or more in column V, then a "B" should be entered.
IF either in either Column U or Column V the time listed is 0-sec to 30-sec (or, equal to 30-sec or less, whatever makes the formula easier) then a "W" should be entered in to Column W

Thanky for any help you may be able to provide.

 

[tnazirov]

Hi,

Sorry, could you specify values cell W depending on values of cells U and V accepts?
Simply fill the form below:

<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=295><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2154791 class=xl65 height=15 width=110>U

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=121>V

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>W

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=15><=30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65><=30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>?

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=15><=30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>>30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>?

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=15>>30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65><=30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>?

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=15>>30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>>30

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>?

</TD></TR></TBODY></TABLE>​

 

[Sandeep Warrier]

What if both U and V have values greater than 31 sec?
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Johnstog]

Maybe this might be easier.

There are a times listed in Columns H & Column F. I need Excel to tell me, which time came first by marking a column with the letter "A", "B" or "W". If the difference of either time is within 30-sec of each other it will mark it with a "W". By doing this it will tell me who arrived first ("A" or "B"). Or, if the units responding arrives within 30-sec of each other, marking the response with a "W".

Step 1 - IF there is a 30-sec difference in time between Columns F or H (positve or negative), then I need a "W" inputted in column W and the formula can end. IF the difference is equal to or greater than 31-sec, it will got to the next step.
Step 2 - IF the time in Column H is larger than Column F then I need an "A" to be inputted in Column W
Step 3 - IF the time in Column H is smaller than Column F then I need a "B" to be inputted in column W

I hope this helps or did this confuse you more.?

 

[Johnstog]

Only U or V will have a time listed. If both columns U & V have a time, then something has calculated wrong.

 

[Johnstog]

Here is another example as to what is on my spreadsheet:


Columns
F / H / ..... / U / V / W
_________________________________________________________
1 05:19:18 05:22:08 0:02:50 ##### A
2 09:20:22 09:19:24 ##### 0:00:58 B
3 11:38:06 11:38:02 ##### 0:00:04 W
4 23:49:22 23:49:40 0:00:18 ##### W

So to get the result in Column W I need Excel to calculate either between columns F & H, or calculate between columns U & V

Calculating between F & H:
- IF Column F is less than Column H then Column W will = "A"
- IF Column F is greater than < Column H then Column W will = "B"
- IF Column F & H are within 30-seconds difference then Column W will = "W"

Calculating between U & V:
- If there is a time of equal to or greater than 31-sec in Column U, then Column W will equal an "A"
- If there is a time of equal to or greater than 31-sec in Column v, then Column W will equal an "B"
- if there is a time in either columns U or V that is equal to or less than 30-sec, then Column W will equal a "W"

Either way that Excel calculates would be fine. If you chose Column F & H to base the formula off of. Notice in row 1 in the example table Column F has a lower time than Column H. The result in Column W would then input an "A". If you chose Columns U & V tot base the formula from. Notice there is a time that is greater than 30-sec in Column U and not Column V. The result in Column W would then input an "A". In rows 3 & 4 there is a time that is less than 30-sec, so Column W would input a "W". Doesn't matter which column (U or V) that the time falls on it will count as a "W" in Column W.

 

[tnazirov]

Hi,

Please my solution to calculate for F and H:

=IF(ABS(SECOND(F1)-SECOND(H1))<=30,"W",IF(SECOND(H1)>SECOND(F1),"A","B"))