String of "IF" Formula's

J

Johnstog

Board Regular
Joined
Sep 11, 2007
Messages
163
I am trying to get a column to recognize different events from another column and Excel to respond to that formula.

for instance:
Column G (is an assigned event number)
1
3
2
Column W (calculates a time frame):
=IF(P4="","",SUM(Q4-M4))

Column X:
This column is to recognize the event in column G, then determine if it was on-time based on the time calculated in Column W. This is the formula I came up with, but unsure if it done correctly.
=IF(G2=1,IF(W2>=0.005555556,"N","Y"),"OK")

Would I continue the formula with a comma and another "IF" statement, or work I incorportae the next event number within the statement?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
your formula means
if G2=1 is true then
you find out if W2 > some number
if answer is true then enter "N"
if false ener "y"
suppose G2=1 is false then enter "ok"
where G2 is not equal to 1 you can enter another if e.g. G=2 then continue.
this called nested if . But you can have only seven nested if inside.
Instead of nestedif you can wirte a macro using "case select" (see excel help)

It all depends upon how many value G can have and perhpa the soltuion can be tweaked.
 
Upvote 0
Sorry, this wasn't as clear as I wanted it to come across and I left some out, so I will just type in what my actual document is trying to do.

In Column G is an event code. I have about 10 codes, but for the sake of keeping it simple, I will stick with these three for now: 911, 911X, OT

Column M is a Recieved Time and Column Q is an on-scene time.

Column W is set up to calculate a difference between Q & M.

Column X needs to recognize the event from Column G, then tell me the following from Column W:

if G2 = 911 then >8min then "N", If it is <8min then "Y"
if G2 = 911x then >12min then "N", If it is <12min then "Y"
if G2 = OT then >15 then "N", If it is <15 then "Y"

I have also included an If statement for the Y column, which tells excel if I put "OK" in that column it will make Column X to say "Y".
 
Upvote 0
It alll depnds upon how M.Q and W values are enterd .eitehr as time for e.g. 0:15(15 minutes) or 0:09
or as decimals 0.08 or 0.15.

for both case I have given two macros. see the comments in the macros at the beginning
post feedback if there is a bug.

if you want you can even convert any of tihese into event code for "change" in G2 only, so that when you change G2 automaticalyl x2 will change.



Code: 
Dim j As Double
Dim g As Range, w As Range, x As Range
Sub test()
'if times in M.Q and W are in time format e.g. 4:40

j = 1 / (24 * 60)
Set g = Range("g2")
Set w = Range("w2")
Set x = Range("x2")
Select Case g
Case "911"
If w > 8 * j Then x = "N"
Case "911k"
If w > 12 * j Then x = "N"
Case "OT"
If w > 15 * j Then x = "N"
Case Else
x = "Y"
End Select
End Sub

Sub test1()
'if times in M,Q and W are in decimals like 8.04 or 8.4

Set g = Range("g2")
Set w = Range("w2")
Set x = Range("x2")
Select Case g
Case "911"
If w > 0.08 Then x = "N"
Case "911k"
If w > 0.12 Then x = "N"
Case "OT"
If w > 0.15 Then x = "N"
Case Else
x = "Y"
End Select
End Sub
 
Upvote 0
I think that the simplest way to accomplish this is for you to set up a table, e.g. in AA2:AA11 list your event codes and in the next column, AB2:AB11 show the time frame allowed, so for your data the first 3 rows of that table would look like this:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>911</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="5.5555555555555558E-3">00:08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>911x</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="8.3333333333333332E-3">00:12</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>OT</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="1.0416666666666666E-2">00:15</TD></TR></TBODY></TABLE>

Pre-format column AA only as text [or enter numeric codes like 911 with a preceding apostrophe, i.e. '911 to make them text]

Then you can use this simple formula

=IF(W2="","",IF(W2>VLOOKUP(G2&"",AA$2:AB$11,2,0)+0,"N","Y"))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Combined formula help
2
Replies
11
Views
382
dreid1011
dreid1011
K
  • Question
Net work Hours Calculation
Replies
5
Views
303
Keyan_rrr
K
R
  • Solved
Ignoring the value of a formula in a cell.
Replies
8
Views
614
Royalbloodi
R
B
  • Question
NETWORKDAY and WORKDAY formulas
Replies
2
Views
478
Dave Patton
D
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
249
RobP
R

Forum statistics

Threads
1,221,604
Messages
6,160,747
Members
451,670
Latest member
Peaches000

Share this page

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
Back
Top