Prev: To deal with #NA
Next: If column A = B then pick up value in D.
From: NoodNutt on 28 Apr 2010 06:00
Hi Everyone

I am trying to create a formula that will provide one of the following:

Arrival Window: 40 Mins = 20 Mins Early to 20 Mins Late.

So this is what I have tried, but it does not equate to "On TIme"

'=IF($Z2=0,"ATTENTION",IF($Z2<$AB2,"EARLY",IF($Z2>$AB2<$AD2,"ON
TIME","LATE")))

Row 1 is reserved for column haedings and some fixed references.

Column Y = "21 Mins" Window
Column Z = Actual Arrival Time.
Column AC = Booked Arrival Time
Column AB = 21 Ealry window equation of AC-$Y$1
Column AD = 21 Late window equation of AC+$Y$1

So, with the above equation, it is meant to read like this:

If Z2 = 0 then Attention is required, but if it doesn't, then it needs to
check if Z fits in between the "Greater Than" AB, but "Less Than" AD, which
should display "On Time", else it is either "Early" or "Late".

So far I can get it to display all but "On Time".

A Huge TIA

Regards
Mark.


From: Bob Phillips on 28 Apr 2010 06:15
Try

=IF($Z2=0,"ATTENTION",IF($Z2<$AB2,"EARLY",IF($Z2<$AD2,"ON TIME","LATE")))

--

HTH

Bob

"NoodNutt" <mclind1(a)bigpond.com> wrote in message
news:OZy1bmr5KHA.4888(a)TK2MSFTNGP06.phx.gbl...
> Hi Everyone
>
> I am trying to create a formula that will provide one of the following:
>
> Arrival Window: 40 Mins = 20 Mins Early to 20 Mins Late.
>
> So this is what I have tried, but it does not equate to "On TIme"
>
> '=IF($Z2=0,"ATTENTION",IF($Z2<$AB2,"EARLY",IF($Z2>$AB2<$AD2,"ON
> TIME","LATE")))
>
> Row 1 is reserved for column haedings and some fixed references.
>
> Column Y = "21 Mins" Window
> Column Z = Actual Arrival Time.
> Column AC = Booked Arrival Time
> Column AB = 21 Ealry window equation of AC-$Y$1
> Column AD = 21 Late window equation of AC+$Y$1
>
> So, with the above equation, it is meant to read like this:
>
> If Z2 = 0 then Attention is required, but if it doesn't, then it needs to
> check if Z fits in between the "Greater Than" AB, but "Less Than" AD,
> which should display "On Time", else it is either "Early" or "Late".
>
> So far I can get it to display all but "On Time".
>
> A Huge TIA
>
> Regards
> Mark.
>


From: NoodNutt on 28 Apr 2010 06:23
Whoa! works a treat.

Thx heaps Bob

Your a champ.

Cheers
Mark.


 | 
Pages: 1
Prev: To deal with #NA
Next: If column A = B then pick up value in D.