Constant Line

[maddo2k8]

Hi, I'm making mutiple charts for a food item, basically I have a line graph with 2 lines, 1 being the results of tests, say salt content 4g, 6g, 3g, 7g; and the 2nd line being the label claim, so a constant of say 5g.

My problem is that I need something that can be used with both excel 2003 and 2007, which shows this constant line at the same length as the number of pieces of data. What I mean is, at the moment, using excel 2003 I have to manually input ={5,5,5,5} to get four points to make a line (as there are 4 points of data from the tests), but when more data is added the line becomes too short.

Thanks for any help (:

[Andy Pope]

Can you not add the constant value to cells and expand the same way as the normal data?

Otherwise a named range could be used.

[maddo2k8]

There's large amounts of data to be made into graphs, i.e. one graph for say water content, one graph for salt, one for vitamin C etc. for mutiple food items.

This means if I put the constant in next to each of these it would mess the layout of the tables for a start, and take up a lot of space in the sheets, making it harder to use for other people.

As for using a named range, I don't see how this would help make a constant line here which is as long as the line of data points, I am wondering whether some sort of IF or COUNT formula could be used to make it the right length

Perhaps that is too complicated to do many times; could you explain about the named range idea please?

Thanks very much for your help

[maddo2k8]

Just as a very brief follow up, I am not sure whether a COUNT(A?) formula could even be used here as when trying to incorporate this sort of formula into the value formula of a series in a graph gives the error message of an incorrect formula.

In retrospect, perhaps to make good quality graphs, the layout could be changed, with a column next to the result in the tables showing the desired value (the constant) which would probably in the long term be a more sustainable method.

Thanks anyway and if you do know how to use a count formula or similar then please say so.

Thanks again

[Andy Pope]

Adding extra columns is the easiest way. The numbers do not have to be in the table they could be way to the right.

Here is an example of a named range plotting a constant value.

The ConstCount and ConstValue are direct links worksheet values.

=(ROW(OFFSET(Sheet1!$A$1,0,0,ConstCount,1))^0)*ConstValue

[maddo2k8]

Wow that's pretty complicated - could you explain what's going on there please ? I understand that ConstCount + ConstValue are the named ranges but why is the formula used ?

And also I can't see what "ConstLine" is referring to exactly, and how the graph uses all this information I'm using excel 2007 at the moment so maybe that's why I can't see something...

Thanks again

Here is an example of a named range plotting a constant value.

The ConstCount and ConstValue are direct links worksheet values.

=(ROW(OFFSET(Sheet1!$A$1,0,0,ConstCount,1))^0)*ConstValue