Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do I really need OFFSET?
I have defined a Dynamic Name as follows:
=OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) The label for the data is in A3, the data starts in A5 and continues downward. The definition above does start plotting from A5 but adds a blank cell. There is always one extra empty data point that is plotted. Is there an easy fix so that the plot starts at A5 and stops at the last data point? Thanks. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do I really need OFFSET?
See whether changing -1 to -2 (two places) fixes the range definition. If
so, you have two non-blank cells that you don't want to count instead of one. One is for a label atop the data in column A, the other would be a cell above that with other contents. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "bflorox" wrote in message ... I have defined a Dynamic Name as follows: =OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) The label for the data is in A3, the data starts in A5 and continues downward. The definition above does start plotting from A5 but adds a blank cell. There is always one extra empty data point that is plotted. Is there an easy fix so that the plot starts at A5 and stops at the last data point? Thanks. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do I really need OFFSET?
Changing to -2 does eliminate the extra blank data point; although there is
only one, not two, blanks between the label and the data. Could COUNTA have been used without OFFSET and be told where to start from somehow? Thanks. "Jon Peltier" wrote: See whether changing -1 to -2 (two places) fixes the range definition. If so, you have two non-blank cells that you don't want to count instead of one. One is for a label atop the data in column A, the other would be a cell above that with other contents. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "bflorox" wrote in message ... I have defined a Dynamic Name as follows: =OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) The label for the data is in A3, the data starts in A5 and continues downward. The definition above does start plotting from A5 but adds a blank cell. There is always one extra empty data point that is plotted. Is there an easy fix so that the plot starts at A5 and stops at the last data point? Thanks. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do I really need OFFSET?
1. You need either OFFSET or INDEX or something similar.
2. Remove extraneous blank rows and columns within the data set. there is only one, not two, blanks between the label and the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "bflorox" wrote in message ... Changing to -2 does eliminate the extra blank data point; although there is only one, not two, blanks between the label and the data. Could COUNTA have been used without OFFSET and be told where to start from somehow? Thanks. "Jon Peltier" wrote: See whether changing -1 to -2 (two places) fixes the range definition. If so, you have two non-blank cells that you don't want to count instead of one. One is for a label atop the data in column A, the other would be a cell above that with other contents. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "bflorox" wrote in message ... I have defined a Dynamic Name as follows: =OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) The label for the data is in A3, the data starts in A5 and continues downward. The definition above does start plotting from A5 but adds a blank cell. There is always one extra empty data point that is plotted. Is there an easy fix so that the plot starts at A5 and stops at the last data point? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Offset | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
OFFSET HELP | Excel Worksheet Functions | |||
Offset. | Excel Worksheet Functions | |||
offset | Excel Worksheet Functions |