Assign Blank or No Value to a Cell

[hydroguy]

I am evaluating precipitation data from different stations, and want to examine how the difference between the stations changes as a function of the average value between the stations. There are missing values for some dates for the different stations which I need to ignore, i.e., assign a blank cell or empty cell to both the average and the difference. I also want to ignore times when no precipitation occurred at both stations. I have attached a sample worksheet in which I have used the following code to try to sort the data, assign blank cells where appropriate, and assign the average value otherwise. I used quotation marks with nothing between them to assign an empty cell if appropriate based on the logical tests.

Please Login or Register  to view this content.

Similarly, I have used the following, virtually identical code approach, to assign a blank cell, if appropriate, or the precipitation difference where my criteria are met.

Please Login or Register  to view this content.

The caluclations appear to be made correctly, but I am getting a green triangle error flag on the first calculated column (the average precipitation). When I try to graph the data, the x-values (which should be the average precipitation values) appear to be assigned a sequential order number rather than the calculated value (see the graph in the attached workbook). I have not been able to figure out why the graphed X-values are not reflecting the calculated values in the "Data" worksheet from which they were graphed.

I tried checking the formating of the cells. The cells were first left formatted as "General". I also tried formatting them as "Number" with various numbers of decimal places and also as "Scientific".

Is something happening based on how I am assigning an empty or blank cell?


12/30/2011

I tried something else. In the code above, rather than try to assign a blank or emptry cell, I assigned a value of 999. Now when I do a scatter plot of the data everything comes out correctly. I just have to reset the values of the X- and Y-scales to see the data that I am interested in examining. The problem is that I will not be able to perform any curve fit to the data nor can I run any statistics on them.

What is happening with the blank cells to cause Excel to treat them as if some valid data is present when it is not?

Just a few minutes ago I copied all the calculated values and did a paste-special as Values Only. Then I cut-and-pasted all the X-Y data pairs to make a completely contiguous set of the data. Now, when I plot these data, the scatterplot works correctly. Apparently Excel is struggling with how to properly handle the blank cells. In the old version of Excel that I used to use, Excel appeared to simply ignore the blank cells. In this case, it seems to be assigning a sequential value for the data-pair to the X-value when there are a lot of empty cells. Is there a way to get around this?

[hydroguy]

I found a solution through another thread entitled "How to assign a cell the 'empty' value". The key was to replace the double-quotes with no space between them with NA() such that the formula for the average becomes

Please Login or Register  to view this content.

The formula for the difference was modified similarly. When I do this, the cells appear with "#N/A" displayed, but when I create a scatterplot, those cells are ignored and the X-values are properly assigned.

I am evaluating precipitation data from different stations, and want to examine how the difference between the stations changes as a function of the average value between the stations. There are missing values for some dates for the different stations which I need to ignore, i.e., assign a blank cell or empty cell to both the average and the difference. I also want to ignore times when no precipitation occurred at both stations. I have attached a sample worksheet in which I have used the following code to try to sort the data, assign blank cells where appropriate, and assign the average value otherwise. I used quotation marks with nothing between them to assign an empty cell if appropriate based on the logical tests.

Please Login or Register  to view this content.

Similarly, I have used the following, virtually identical code approach, to assign a blank cell, if appropriate, or the precipitation difference where my criteria are met.

Please Login or Register  to view this content.

The caluclations appear to be made correctly, but I am getting a green triangle error flag on the first calculated column (the average precipitation). When I try to graph the data, the x-values (which should be the average precipitation values) appear to be assigned a sequential order number rather than the calculated value (see the graph in the attached workbook). I have not been able to figure out why the graphed X-values are not reflecting the calculated values in the "Data" worksheet from which they were graphed.

I tried checking the formating of the cells. The cells were first left formatted as "General". I also tried formatting them as "Number" with various numbers of decimal places and also as "Scientific".

Is something happening based on how I am assigning an empty or blank cell?


12/30/2011

I tried something else. In the code above, rather than try to assign a blank or emptry cell, I assigned a value of 999. Now when I do a scatter plot of the data everything comes out correctly. I just have to reset the values of the X- and Y-scales to see the data that I am interested in examining. The problem is that I will not be able to perform any curve fit to the data nor can I run any statistics on them.

What is happening with the blank cells to cause Excel to treat them as if some valid data is present when it is not?

Just a few minutes ago I copied all the calculated values and did a paste-special as Values Only. Then I cut-and-pasted all the X-Y data pairs to make a completely contiguous set of the data. Now, when I plot these data, the scatterplot works correctly. Apparently Excel is struggling with how to properly handle the blank cells. In the old version of Excel that I used to use, Excel appeared to simply ignore the blank cells. In this case, it seems to be assigning a sequential value for the data-pair to the X-value when there are a lot of empty cells. Is there a way to get around this?

I found a solution through another thread entitled "How to assign a cell the 'empty' value". The key was to replace the double-quotes with no space between them with NA() such that the formula for the average becomes

Please Login or Register  to view this content.

The formula for the difference was modified similarly. When I do this, the cells appear with "#N/A" displayed, but when I create a scatterplot, those cells are ignored and the X-values are properly assigned.