If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
use a formula to create a empty cell
I am plotting weekly averages on a line graph in Excel XP. I enter the weekly
numbers above a cell (g30) containing this formula: =if(count(g23.g29)=0,"",average(g23.g29)) and plot that result against the date in cell g31. The next week the data goes into the range h23.h29, the averaging formula is in cell h30, and the date is in cell h31, etc. I miss the occasional week of data. When I do this, the cell with the averaging formula eg cell h30, shows a blank. All good. BUT, the graph recognizes the result of the formula as zero not as empty, regardless that I chose to "Plot Empty Cells as Leave Blank" in Chart Options. It has the result of sending the plot of my series way down to zero and then back up again. This looks bad, and ruins my trendline! So, Excel is interpretting the result "" as zero, rather than as an empty cell. HOw can I modify my formula or chart so that when I don't have any data, I get an empty cell rather than "" or hte value zero? Thanks |
#2
|
|||
|
|||
Hi,
You need to use NA() instead of "" in your formula. =if(count(g23.g29)=0,NA(),average(g23.g29)) Cheers Andy Chas wrote: I am plotting weekly averages on a line graph in Excel XP. I enter the weekly numbers above a cell (g30) containing this formula: =if(count(g23.g29)=0,"",average(g23.g29)) and plot that result against the date in cell g31. The next week the data goes into the range h23.h29, the averaging formula is in cell h30, and the date is in cell h31, etc. I miss the occasional week of data. When I do this, the cell with the averaging formula eg cell h30, shows a blank. All good. BUT, the graph recognizes the result of the formula as zero not as empty, regardless that I chose to "Plot Empty Cells as Leave Blank" in Chart Options. It has the result of sending the plot of my series way down to zero and then back up again. This looks bad, and ruins my trendline! So, Excel is interpretting the result "" as zero, rather than as an empty cell. HOw can I modify my formula or chart so that when I don't have any data, I get an empty cell rather than "" or hte value zero? Thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with difference between blank and empty cell | balraj | Worksheet Functions | 1 | May 15th, 2004 10:53 AM |
filling empty cell if not empty keep its content!? | Norman Harker | Worksheet Functions | 3 | February 23rd, 2004 08:48 AM |
Sheet Names | Joseph M. Yonek | Worksheet Functions | 6 | January 3rd, 2004 02:15 AM |
Formula help | Wally | Worksheet Functions | 6 | December 5th, 2003 12:18 PM |
How to create formula for combining two cells as one cell | [email protected] | Worksheet Functions | 2 | November 10th, 2003 08:27 PM |