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.

 

[finnexcel]

Finally back at work after being ill, just found this thread again.


Im really close - but the last file does not work with by (admittedly scrappy) time date

My data looks like the below - and I've copied it to the LookupTimesV2.xls 18kb file - but it still doesnt work - so am guessing its to do with my time data

have copied below attached what my time data looks like when formatted as text (in my sheet it's correctly formatted as time.

* Does the formula in the LookupTimes need changing to make it work?
* Or wont it work with my date and I need to clean/parse it somehow?

Thanks all again - Good to be back and been watching lots of 'ExcelIsFun" videos - so getting better. (if the Excel is fun guy reads this - you are my hero!)


-0.008333333
-0.00625
-0.002222222
#VALUE!
-0.007638889
-0.007638889
-0.000671296
#VALUE!
0.006608796
-0.004166667
-0.006944444
-0.002083333
-0.001388889
-0.000694444
-0.005555556
-0.007696759
-0.003472222
0.0125
-0.004166667
0.000694444
-0.011111111
-0.002083333
#VALUE!
-0.002777778
-0.004293981
-0.002777778
-0.002083333
-0.002118056

 

[T. Valko]

Finally back at work after being ill, just found this thread again.


Im really close - but the last file does not work with by (admittedly scrappy) time date

My data looks like the below - and I've copied it to the LookupTimesV2.xls 18kb file - but it still doesnt work - so am guessing its to do with my time data

have copied below attached what my time data looks like when formatted as text (in my sheet it's correctly formatted as time.

* Does the formula in the LookupTimes need changing to make it work?
* Or wont it work with my date and I need to clean/parse it somehow?

Thanks all again - Good to be back and been watching lots of 'ExcelIsFun" videos - so getting better. (if the Excel is fun guy reads this - you are my hero!)


-0.008333333
-0.00625
-0.002222222
#VALUE!
-0.007638889
-0.007638889
-0.000671296
#VALUE!
0.006608796
-0.004166667
-0.006944444
-0.002083333
-0.001388889
-0.000694444
-0.005555556
-0.007696759
-0.003472222
0.0125
-0.004166667
0.000694444
-0.011111111
-0.002083333
#VALUE!
-0.002777778
-0.004293981
-0.002777778
-0.002083333
-0.002118056

Not sure what you're asking here.

Do you want to extract all the data excluding the errors?

 

[finnexcel]

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.

I'd like to just have the Sheet one example you gave to work with the data I have - I know all your stuff works - it just doesn't work with my data properly.

It counts fine - and returns the right number (nine) - but cannot return the correct reference number values - I still get the NUM error.

I'm using excel 2010 - and have just copied my data into the example you had created so it should work? - But doesn't using the data I copied in last post.

Is this all correct? (that my data copied into your example, using excel 2010 should work) or am I missing something?

thanks again!

EDIT: - Have attached my ugly looking W column using the upload service thing if this helps
http://cjoint.com/12av/BDor7vd8DCd.htm

 

[T. Valko]

I'd like to just have the Sheet one example you gave to work with the data I have - I know all your stuff works - it just doesn't work with my data properly.

It counts fine - and returns the right number (nine) - but cannot return the correct reference number values - I still get the NUM error.

I'm using excel 2010 - and have just copied my data into the example you had created so it should work? - But doesn't using the data I copied in last post.

Is this all correct? (that my data copied into your example, using excel 2010 should work) or am I missing something?

thanks again!

EDIT: - Have attached my ugly looking W column using the upload service thing if this helps
http://cjoint.com/12av/BDor7vd8DCd.htm

Ok, I downloaded the file but I don't know what I'm supposed to do with it?

There are no formulas, just a column with some #VALUE! errors, a few time values and a lot of "hash" marks because Excel won't display negative times/dates by default.

What are you wanting to do with this data?

 

[finnexcel]

Sorry to be so thick.

I'd like the attached file to correctly pass along the values.

Have only pulled down the data01, data02 columns - and replaced the example times with my real list of (messy) timevalues

The count is correct

I just wanted it to be able to pass the correct reference from column d to column aa

(not sure why it returns Data1 in cell aa2 either?)

http://cjoint.com/12av/BDotsYcT0Al.htm

 

[T. Valko]

Sorry to be so thick.

I'd like the attached file to correctly pass along the values.

Have only pulled down the data01, data02 columns - and replaced the example times with my real list of (messy) timevalues

The count is correct

I just wanted it to be able to pass the correct reference from column d to column aa

(not sure why it returns Data1 in cell aa2 either?)

http://cjoint.com/12av/BDotsYcT0Al.htm

OK, a couple of problems.

The count formula is referencing the ENTIRE column range while the extraction formulas were only referencing down to row 16.

Also, the extraction formulas are array formulas but were not entered as array formulas.

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.

Here's your file with the updated range references in the array formulas.

Almost_There(2).xls

http://cjoint.com/?BDowjXHAlPJ

Just curious. Why are so many of the entries negatives?