Count

[Brian from Maui]

Aloha,

I would like to count these events as 1. Lost again

d1-c1>30
c1>a1
d1>b1

Mahalo

 

[Derek]

Hi
I assume you are looking for an If statement.

=IF(AND(D1-C1>30,C1>A1,C1>A1), "true", "false")

Replace "true" with what you want to see in the cell if its true.
Replace "false" with what you want to see in your cell if its false

Hope this is what you are looking for
Derek

 

[Brian from Maui]

Derek,

Not quite. I have time values in ABCD

10:00 10:40 10:10 10:41

It meets the criteria, but doesn't count as 1

Mahalo

 

[anno]

question - if you have time values in the cells, how will D1-C1>30 ever be true? am i missing something? do you have dates as well?

 

[Brian from Maui]

d1-c1 is 31 mins

 

[anno]

oh, i see. i thought it would count that as .517. have never been good with time formats...thanks

 

[anno]

hang on. =D1-31 returns .31, not 31, so you will have amend the test condition from 30 to .30 (or will it be .5?)

 

[Brian from Maui]

I tried formatting target cell to number, general, and time..no go. changed 31 to .31 and :31 doesn't count it ..

 

[Aladin Akyurek]

On 2002-02-28 20:31, Brian from Maui wrote:
Aloha,

I would like to count these events as 1. Lost again

d1-c1>30
c1>a1
d1>b1

Mahalo

Brian,

Consider the following sample, where the funny numbers are Excel's internal representation of actual times,

{0.416666666666667,0.444444444444444,0.423611111111111,0.445138888888889;
0.40625,0.427083333333333,0.409722222222222,0.430555555555556;
0.427083333333333,0.447916666666667,0.430555555555556,0.456944444444444}

in A1:D3.

In E1 enter & copy down:

=((D1-C1)*1440>30)*(C1>A1)*(D1>B1)

You can then sum column E to get desired count:

=SUM(E:E)

or

=SUM(E1:E3)

However, you can also arrive at the desired count by means of a single formula:

In F1 enter:

=SUMPRODUCT(((D1:D3-C1:C3)*1440>30)*(C1:C3>A1:A3)*(D1:D3>B1:B3))

Aladin
This message was edited by Aladin Akyurek on 2002-03-01 00:35

 

[Brian from Maui]

Aladin,

"AWESOME"



Did you read my funny story?