graph function

[alex]

hello,

perhaps anybody knows how to do this:

1. i have few rows of data. in the last column there is a formula that
calculates some value for the specific row based on values in different
columns of this row and one extra value which is located somewhere and is the
same for all rows.

2. then there is a cell with total of the last column.

i want to build a chart that will have as

Y - the total cell (2.) value
X - will take the value of the extra cell and change it in a range of
plus/minus 20
for example

and chart a graph of this.

thank you,

Alex.

[Jon Peltier]

Alex -

It is possible to write defined formulas that compute various values,
then plot using these formulas, but I advise against it. Do the
calculations in the worksheet, and plot the range with the calculations.
Columns are cheaper than your sanity.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


alex wrote:

> hello,
>
> perhaps anybody knows how to do this:
>
> 1. i have few rows of data. in the last column there is a formula that
> calculates some value for the specific row based on values in different
> columns of this row and one extra value which is located somewhere and is the
> same for all rows.
>
> 2. then there is a cell with total of the last column.
>
> i want to build a chart that will have as
>
> Y - the total cell (2.) value
> X - will take the value of the extra cell and change it in a range of
> plus/minus 20
> for example
>
> and chart a graph of this.
>
> thank you,
>
> Alex.

[alex]

Thanks for reply.

this is exactly what i dont want to do - to do the calculations in the
worksheet - because of 2 reasons:

1. - the data is rather complicated, and lets say if i have 5 rows - this
makes the range at least 5-6 or 7 columns.
2. i have about 50 different combinations or more of about 5 rows each, and
for every combination the "extra" value is different.
3. it will be complicated for user to work with ranges....so i want to have
a button that when the user clicks - draws a chart, without creating the
range and calculation in the worksheet.


thanks,

Alex.
"Jon Peltier" wrote:

> Alex -
>
> It is possible to write defined formulas that compute various values,
> then plot using these formulas, but I advise against it. Do the
> calculations in the worksheet, and plot the range with the calculations.
> Columns are cheaper than your sanity.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> alex wrote:
>
> > hello,
> >
> > perhaps anybody knows how to do this:
> >
> > 1. i have few rows of data. in the last column there is a formula that
> > calculates some value for the specific row based on values in different
> > columns of this row and one extra value which is located somewhere and is the
> > same for all rows.
> >
> > 2. then there is a cell with total of the last column.
> >
> > i want to build a chart that will have as
> >
> > Y - the total cell (2.) value
> > X - will take the value of the extra cell and change it in a range of
> > plus/minus 20
> > for example
> >
> > and chart a graph of this.
> >
> > thank you,
> >
> > Alex.
>

[Jon Peltier]

In my experience, the more complicated the data, the more important it
is to do the calculations in the worksheet (or alternatively to
calculate using VBA, and inserting the results into the sheet). You can
write code to automatically make a chart from this data (i.e., code
behind a button), and you may want to hide the data in hidden columns or
on a hidden sheet to keep it safe from unwitting changes by a confused user.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


alex wrote:

> Thanks for reply.
>
> this is exactly what i dont want to do - to do the calculations in the
> worksheet - because of 2 reasons:
>
> 1. - the data is rather complicated, and lets say if i have 5 rows - this
> makes the range at least 5-6 or 7 columns.
> 2. i have about 50 different combinations or more of about 5 rows each, and
> for every combination the "extra" value is different.
> 3. it will be complicated for user to work with ranges....so i want to have
> a button that when the user clicks - draws a chart, without creating the
> range and calculation in the worksheet.
>
>
> thanks,
>
> Alex.
> "Jon Peltier" wrote:
>
>
>>Alex -
>>
>>It is possible to write defined formulas that compute various values,
>>then plot using these formulas, but I advise against it. Do the
>>calculations in the worksheet, and plot the range with the calculations.
>>Columns are cheaper than your sanity.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>alex wrote:
>>
>>
>>>hello,
>>>
>>>perhaps anybody knows how to do this:
>>>
>>>1. i have few rows of data. in the last column there is a formula that
>>>calculates some value for the specific row based on values in different
>>>columns of this row and one extra value which is located somewhere and is the
>>>same for all rows.
>>>
>>>2. then there is a cell with total of the last column.
>>>
>>>i want to build a chart that will have as
>>>
>>>Y - the total cell (2.) value
>>>X - will take the value of the extra cell and change it in a range of
>>>plus/minus 20
>>> for example
>>>
>>>and chart a graph of this.
>>>
>>>thank you,
>>>
>>>Alex.
>>