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.

=IF(ISBLANK(B11),"",IF(ISBLANK(C11),"",IF(AND(B11=0,C11=0),"",AVERAGE(B11:C11))))

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.

=IF(ISBLANK(B11),"",IF(ISBLANK(C11),"",IF(AND(B11=0,C11=0),"",ABS(B11 - C11))))

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

=IF(ISBLANK(B11),NA(),IF(ISBLANK(C11),NA(),IF(AND(B11=0,C11=0),NA(),AVERAGE(B11:C11))))

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.

=IF(ISBLANK(B11),"",IF(ISBLANK(C11),"",IF(AND(B11=0,C11=0),"",AVERAGE(B11:C11))))

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.

=IF(ISBLANK(B11),"",IF(ISBLANK(C11),"",IF(AND(B11=0,C11=0),"",ABS(B11 - C11))))

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

=IF(ISBLANK(B11),NA(),IF(ISBLANK(C11),NA(),IF(AND(B11=0,C11=0),NA(),AVERAGE(B11:C11))))

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.