COUNTIF - tracing results for further action

[finnexcel]

I am working from the difference between two times.

If the difference is >15 or >20 - different events occur.

I can use =COUNTIF(W:W,">0.0138888888888889") to count orders later than 20 minutes for example.

In this instance - there were 9.

But, I'd like to also return the value of column D - where the value in W is > 20mins,

I know excel knows what these rows are as the count is correct - but how can I get the Dn to return somewhere when the Wn is more than 20mins?

I hope this makes sense.

 

[circledchicken]

Hi,

Perhaps filter column W for greater than 20 mins and you will see all the corresponding values in D?

 

[T. Valko]

I am working from the difference between two times.

If the difference is >15 or >20 - different events occur.

I can use =COUNTIF(W:W,">0.0138888888888889") to count orders later than 20 minutes for example.

In this instance - there were 9.

But, I'd like to also return the value of column D - where the value in W is > 20mins,

I know excel knows what these rows are as the count is correct - but how can I get the Dn to return somewhere when the Wn is more than 20mins?

I hope this makes sense.

Try this...

Book1
	D	W	X	Y	Z	AA
2	Data1	0:17	_	Time	0:20	Data2
3	Data2	0:25	_	Count	5	Data4
4	Data3	0:03	_	_	_	Data7
5	Data4	1:11	_	_	_	Data9
6	Data5	0:19	_	_	_	Data13
7	Data6	0:02	_	_	_	_
8	Data7	1:22	_	_	_	_
9	Data8	0:06	_	_	_	_
10	Data9	0:21	_	_	_	_
11	Data10	0:15	_	_	_	_
12	Data11	0:15	_	_	_	_
13	Data12	0:16	_	_	_	_
14	Data13	0:37	_	_	_	_
15	Data14	0:18	_	_	_	_
16	Data15	0:04	_	_	_	_
Sheet1

Enter this formula in Z3:

=COUNTIF(W:W,">"&Z2)

Enter this array formula** in AA2:

=IF(ROWS(AA$2:AA2)>Z$3,"",INDEX(D:D,SMALL(IF(W$2:W$16>Z$2,ROW(W$2:W$16)),ROWS(AA$2:AA2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[finnexcel]

Is there any way I can use a formula to do this?

I'm stuck - not possible to manually/visually look up the references.

 

[finnexcel]

Wow!

Thanks very much - all that code hurts my brain but is amazing

I think I have it working - the results as follows:

0.01388888889 = 20minutes

Does the fact it gets the right answer - 9 mean it works?

Any ideas the reason the value is all weird like that is? (I have my intended results formatted as numbers)



0.01388888889 #VALUE!
9 #VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!



*edit* meant to copy the sheet in all cool like you did - didnt work

 

[T. Valko]

Wow!

Thanks very much - all that code hurts my brain but is amazing

I think I have it working - the results as follows:

0.01388888889 = 20minutes

Does the fact it gets the right answer - 9 mean it works?

Enter the time as a time. Like this 0:20.

Here's a small sample file that demonstrates this.

LookupTimes.xls 17kb

http://cjoint.com/?BCFp4pUhaFY

The formula that extracts the data is an array formula.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

 

[finnexcel]

Thanks Biff! - I've changed Z2 to a time - and it still worked - and counted 9

I also double checked I was entering the array right - I have the right brackets 100%

Is it possible a format error - This #VALUE stuff used to happen a lot when I was messing up VLOOKUPS


Tried to trace error:

"encountered tracer errors or circular reference"

My W column does contain instances of #VALUE - Could that be messing it up?

 

[T. Valko]

Thanks Biff! - I've changed Z2 to a time - and it still worked - and counted 9

I also double checked I was entering the array right - I have the right brackets 100%

Is it possible a format error - This #VALUE stuff used to happen a lot when I was messing up VLOOKUPS


Tried to trace error:

"encountered tracer errors or circular reference"

My W column does contain instances of #VALUE - Could that be messing it up?

Yes, that's probably what's giving you the errors.

Here's the updated version:

Book1
	D	W	X	Y	Z	AA
2	Data1	0:17	_	Time	0:20	Data2
3	Data2	0:25	_	Count	5	Data4
4	Data3	0:03	_	_	_	Data7
5	Data4	1:11	_	_	_	Data9
6	Data5	0:19	_	_	_	Data13
7	Data6	#VALUE!	_	_	_	_
8	Data7	1:22	_	_	_	_
9	Data8	0:06	_	_	_	_
10	Data9	0:21	_	_	_	_
11	Data10	0:15	_	_	_	_
12	Data11	#VALUE!	_	_	_	_
13	Data12	0:16	_	_	_	_
14	Data13	0:37	_	_	_	_
15	Data14	0:18	_	_	_	_
16	Data15	0:04	_	_	_	_
Sheet1

This array formula** entered in AA2:

=IF(ROWS(AA$2:AA2)>Z$3,"",INDEX(D:D,SMALL(IF(ISNUMBER(W$2:W$16),IF(W$2:W$16>Z$2,ROW(W$2:W$16))),ROWS(AA$2:AA2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[finnexcel]

Thanks again.

Something changed in that the #VALUE error is now a #NUM error

Still counts correctly and results in 9 -my D column references are definitely numbers.

Any ideas? ( I did read around and try and correct this myself - haven't got anywhere - the references are 6 digit - so not too big, or negative)

Any ideas?

 

[T. Valko]

Thanks again.

Something changed in that the #VALUE error is now a #NUM error

Still counts correctly and results in 9 -my D column references are definitely numbers.

Any ideas? ( I did read around and try and correct this myself - haven't got anywhere - the references are 6 digit - so not too big, or negative)

Any ideas?

You'll get the #NUM! error a couple of ways...

If you don't array enter the extraction formulas.

If you use entire columns as range references in the array formulas and you're using Excel version 2003 or earlier.

Here's the updated sample file that accounts for the #VALUE! errors.

LookupTimesV2.xls 18kb

http://cjoint.com/?BCFxthGD1mQ