MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data Sets


Posted by Mike Antinori on December 13, 2001 8:46 AM

My problem is that I have about 50,000 cells worth of time vs. temp data. I would like to graph the data. My problem is that I only want to take every 20 data points.

Is there a way to select every 20 cells automatically? Or to divide the data every 20?

Thanks


Posted by Mark W. on December 13, 2001 9:40 AM

Suppose the 1st 22 rows (A1:B22) of your data list
are...

{"Time","Temp"
;"25:16",49
;"16:59",89
;"22:09",88
;"22:54",93
;"45:47",23
;"24:30",79
;"15:20",45
;"27:08",21
;"57:03",66
;"53:56",38
;"48:24",38
;"39:16",47
;"39:23",89
;"21:00",50
;"07:04",43
;"28:20",6
;"00:56",30
;"38:11",72
;"59:33",80
;"40:16",41
;"23:41",17}

...you could add a selector in column C using the
formula, =IF((C1=20)+0,1,N(C1)+1), and then
apply an Autofilter to this column to display
all rows with a 1 or a 20 -- you decide! Your
chart will only include visible data points.


Posted by Mark W. on December 13, 2001 9:42 AM

Revision...

:I would also like to have the formula add an additional $.50 for every call above 8 (eg:at call 9, add $.50 to K24, at call 10 add $.50 to K24, etc). Can this be done?

Thanks.


:Worked like a charm, Thanks Juan!!

Posted by Mark W. on December 13, 2001 9:55 AM

[REPOST] Revision...

ERR

Posted by Mike Antinori on December 13, 2001 10:29 AM

Re: [REPOST] Revision...

That formula just adds 1 to my data. Here it is:

Time Vo DC Plate Temp C
6:46:30 PM 2.4900 55.70
6:46:31 PM 2.4900 55.60
6:46:31 PM 2.5000 55.50
6:46:32 PM 2.5000 55.30
6:46:32 PM 2.4900 55.20
6:46:33 PM 2.4900 55.00
6:46:33 PM 2.4900 54.90
6:46:34 PM 2.4900 54.80
6:46:34 PM 2.4900 54.70
6:46:35 PM 2.4900 54.70
6:46:35 PM 2.5000 54.60
6:46:36 PM 2.5000 54.60
6:46:36 PM 2.4900 54.60
6:46:37 PM 2.4900 54.60
6:46:37 PM 2.4900 54.60
6:46:38 PM 2.4900 54.60
6:46:38 PM 2.4900 54.60
6:46:39 PM 2.4900 54.60
6:46:39 PM 2.4900 54.60
6:46:40 PM 2.4900 54.60
6:46:40 PM 2.4900 54.60
6:46:41 PM 2.4900 54.70
6:46:41 PM 2.4900 54.70
6:46:42 PM 2.4900 54.70


I would like to ba able to select every 20th row (i.e. Row 20, 40, 60... etc.)

The AutoFilter sounds good, but i just need the formula to put in Column D.

Thanks for your help.

Mike

Posted by Mark W. on December 13, 2001 10:57 AM

More specific instructions...

If your data list resides in cells A1:C25...

1. Enter the formula, =IF(D1=20,1,N(D1)+1), into
cell D2.
2. Select D2:D25 and choose the Edit | Fill | Down
menu command
3. Apply an AutoFilter to column D that displays
(filters for) rows with 20 as their value. That formula just adds 1 to my data. Here it is: Time Vo DC Plate Temp C