Length & Distance formula

[Dale G]

Hi,
I need a formula that will count based on the length of a vehicle, and a
timed distance (counted in seconds) the vehicle is behind another vehicle.
Here’s the breakdown;
Satisfactory result for 40’ => 6 sec, 60' = >8 sec
Less than Satisfactory 40' => 3 sec & <6 sec, & 60'= > 4 sec & <8 sec
NOT Satisfactory 40' = 3 sec or less & 60' = 4 sec or less
40’ vehicles can be separated from the 60’ by the vehicle numbers.
All 40’ vehicle numbers will start with 24, 90, and 91.
So I have 3 columns to place a count. Satisfactory in Q3, less than
Satisfactory in R3, and Not Satisfactory in S3.
The vehicle numbers are recorded in column C, and the following distance in
seconds is recorded in G.
If C3 is blank then all will remain blank.
Also, if possible I would like to set a conditional format in column G.
Green for satisfactory, yellow for less than satisfactory and red for not
satisfactory.
Any help is appreciated.
Dale

 

[Bernard Liengme]

Suppose you data is in rows 10 to 100

We need a way of know the vehicle length
In H10 enter =(LEFT(C10,2)="24")+(LEFT(C10,2)="90")+(LEFT(C10,2)="91") and
copy down the column
The 40 footer will return 1, the other will return 0

In Q3 to count all Satisfactory:
=SUMPRODUCT(--(H10:H100=1),--(G10:G100>=6))+SUMPRODUCT(--(H10:H100=0),--(G10:G100>=8))
In S3 for Less than Satisfactory
=SUMPRODUCT(--(H10:H100=1),--(G10:G100<6),--(G10:G100>=3))+SUMPRODUCT(--(H10:H100=0),--(G10:G100<8),--(G10:G100>=4))
In R3 fro Unsatisfactory
=COUNTA(C10:C100)-(Q3+R3)

To hide results if C3 is blank, in Q3 use
=IF(C3="","",=SUMPRODUCT(--(H10:H100=1),--(G10:G100>=6))+SUMPRODUCT(--(H10:H100=0),--(G10:G100>=8)))
Likewise in other cells

For the green condition in G column; select all the G data and use this
formula in thee conditional formatting dialog
=OR(AND($H10=1,G10>=6),AND($H10=0,G10>=8)) and set fill or text to green
For yellow
=OR(AND($H10=1,G10<6,G10>=3),AND($H10=0,G10<8.G10>=4))
For red
=OR(AND($H10=1,G10<3),AND($H10=0,G10<4))

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
REMINDER: These newsgroups are about to die. We will all meet again at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel

 

[Dale G]

Bernard, Thank you.
These formulas are excellent & work very well. I started at the top & copied
them all the way past my existing data. Now when I add the info all is well.
Only one thing; when I pass my existing data to rows that are not filled with
time and vehicle info the conditionally formatted column is red.
Is there a way to have the column appear blank until after the data is
entered?
Here’s what I’ve done so far.
=(LEFT(C3,2)="24")+(LEFT(C3,2)="90")+(LEFT(C3,2)="91"
=IF(C3="","",SUMPRODUCT(--(W3:H3=1),--(G3:G3>=6))+SUMPRODUCT(--(W3:H3=0),--(G3:G11000>=8))
=IF(C3="","",SUMPRODUCT(--(W3:H3=1),--(G3:G3<6),--(G3:G3>=3))+SUMPRODUCT(--(W3:H3=0),--(G3:G38),--(G3:G3>=4))
=COUNTA(C3:C3)-(Q3+R3)
=OR(AND($W3=1,G3>=6),AND($W3=0,G3>=8)) green
=OR(AND($W3=1,G3<6,G3>=3),AND($W3=0,G3<8,G3>=4)) yellow
=OR(AND($W3=1,G3<3),AND($W3=0,G3<4)) red

 

[Dale G]

I got it.

=OR(AND($G3<>"",$W3=1,G3<3),AND($G3<>"",$W3=0,G3<4))

Thanks again.