Formula

Johnstog

Board Regular
Joined
Sep 11, 2007
Messages
163
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

tnazirov

New Member
Joined
Dec 15, 2009
Messages
39
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

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
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

Board Regular
Joined
Sep 11, 2007
Messages
163
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

Board Regular
Joined
Sep 11, 2007
Messages
163

ADVERTISEMENT

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

Johnstog

Board Regular
Joined
Sep 11, 2007
Messages
163
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

New Member
Joined
Dec 15, 2009
Messages
39
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top