I haven't found an Excel expert who can help me, yet!
I am measuring (at 1-minute intervals) a heating system fan's power and the warm air temperature in a duct, produced by the the heating system.
I want to put the power and temperature data into an Excel Chart, but only show the duct temperature when the fan is running. I don't want the temperature line to drop to zero when the fan is off, or show the stagnant-air temperature.
I need an excel function that will do the following:
IF (fan power > 0) then put the temperature measurement into an adjacent (empty) cell. BUT, IF (fan power = 0) put nothing in the adjacent cell. I don't want a zero in the adjacent cell.
That way, the plot line of temperature will be discontinuous, only showing air temperature on the chart when air is flowing through the duct.
Thanks!!!
* Last updated by: RJ on 2/11/2009 @ 11:57 AM *
I can't do what you want in Excel, but I think I can get you there with Split. Split ships in LoggerNet & PC400.
Let's say that I am looking at temperature data (because I am :) My data file includes a timestamp (table based), record number, first temp & second temp. Split allows you to do Range checking to specify a range of "good" values (see the Split help on Range checking). Anything that falls outside of the range is considered a "bad" value.
In my Split select line I type:
1,2,3,4,4[24.1..24.5]*0.0+3
Where:
1 = timestamp
2 = record number
3 = temp 1
4 = temp 2
4[24.1..24.5] = the "good" range of temperature between 24.1 and 24.5
and the *0.0+3 is a round-about way to take the value returned by the range check, multiply it by 0, and add element number 3, thus, effectively giving us just element #3. (In split, all numbers are assumed element numbers unless they have a decimal; e.g., 3.0 is an integer & 3 would be the element).
When I run this in Split, I get a file that looks like the following:
"2009-02-05 12:47:00",98,24.08,24.03,
"2009-02-05 12:48:00",99,24.07,24.03,
"2009-02-05 12:49:00",100,24.1,24.13,24.1
"2009-02-05 12:50:00",101,24.19,24.11,24.19
"2009-02-05 12:51:00",102,24.22,24.2,24.22
"2009-02-05 12:52:00",103,24.16,24.05,
"2009-02-05 12:53:00",104,23.97,23.99,
"2009-02-05 12:54:00",105,24.15,24.07,
"2009-02-05 12:55:00",106,23.88,23.83,
"2009-02-05 12:56:00",107,24.02,24,
"2009-02-05 12:57:00",108,24.23,24.21,24.23
"2009-02-05 12:58:00",109,24.21,24.22,24.21
"2009-02-05 12:59:00",110,24.17,24.09,
Notice that lines that fall outside the range terminate with the comma. Lines that fall within the range have the result of "range check * 0.0 + 3" (which essentially = element # 3).
Split creates a PRN file by default. You can alternately give this file a CSV extension, which is handy in your case for importing into Excel. Make sure the Split output format is specified as comma separated -- default is field separated.
In your instance, the range could be [0.1..99] or so to get the > 0. Sorry I didn't use your scenario for my example, but I wanted to test with something I had :)
I hope this helps,
Dana
There's no way for a formula to put "nothing" in a cell, but you can achieve what you are trying to do by putting "NA#" in the cell. Example: =IF(CL10>5,CN10,NA()) If CL10<=5, "NA#" will show up in the cell. NA# is not graphed (just like an empty cell).
GBarker wrote: "NA# is not graphed (just like an empty cell)."
I so wish na() worked the way GBarker said it does. Indeed, Excel will leave out the data points, but if you are connecting the data points with lines, a line is still drawn across the gap in data between the two data points on either side of the gap. If one of the #N/As is replaced with a truly empty cell, then the graphs behaves as desired.
This has been very frustrating for me for many years. Excel used to treat the #N/A the way we wish it would and I counted on it for graphing CSV data. Then one day many years ago, after installing an upgrade to Excel, it no longer did this. With extensive searching, I could find no way around it in Excel other than manually inserting a blank cell. All my Excel templates that used this were broken from that point forward and I have never found a way around this. I suppose one could write a macro to replace all #N/A cells with blank cells.
If anyone else has an insight into this, it would be greatly appreciated.
The way to get a discontinuous line is to have a blank row in the data. For example:
time temperature
9:01 10
9:02 11
9:03 12
9:23 23
9:24 25
9:25 24
When you plot the data, a line will be drawn for the first three points from 9:01 through 9:30. Then it will skip to 9:23 and start the next part of the plot.
There a number of ways to get the blank lines inserted. I have a macro that looks at the time difference and if it sees more than one minute, it inserts one blank line.
Hope this helps.