Using Formulas for Charts

business models

New Member
Joined
Dec 14, 2012
Messages
8
Using Excel 2007, I need to update numerous charts on a monthly basis. All data is contained within 1 workbook with numerous worksheets. The charts present information from multiple worksheets. I am searching for a way to use a formula that defines the number of data points being charted. ALL worksheets follow a similar format:
Variable name is in A1 (i.e., revenue); monthly data begins in col E and continues.

I created a formula that allows me to define the range desired using defined names. How do I incorporate this formula in a chart? The formula is valid within the "SUM" function; but i need to utilize it within a chart.

=SUM(INDIRECT(ADDRESS(117,Begin_Mo_History,,TRUE,"ISHistory")&":"&ADDRESS(117,End_Mo_History,,TRUE, ) ) )

Defined names: Begin_Mo_History = 5 (col E); End_Mo_History = 46 (col AT)

My goal is to reproduce the following range ('IS History'!$E$117:$AT$117) in a chart using the formula above. Can this be done?

<tbody>
</tbody>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You don't need to specify the beginning and ending cells through defined names. Instead, you can have Excel compute the number of cells to plot. Also, no need for INDIRECT(ADDRESS(...))

Use OFFSET or INDEX.

So, if you have no data in row 117 other than what you want to plot in E117:{whatever}117, create a named formula
OFFSET(e117,0,0,1,COUNTA(117:117)). Use this name in a chart.

For details see Dynamic Charts

There are links to other pages that explain how to create a named formula and how to use a named formula in a chart.

The examples all work with data in rows rather than tables but those references are easy to adjust. :)




Using Excel 2007, I need to update numerous charts on a monthly basis. All data is contained within 1 workbook with numerous worksheets. The charts present information from multiple worksheets. I am searching for a way to use a formula that defines the number of data points being charted. ALL worksheets follow a similar format:
Variable name is in A1 (i.e., revenue); monthly data begins in col E and continues.

I created a formula that allows me to define the range desired using defined names. How do I incorporate this formula in a chart? The formula is valid within the "SUM" function; but i need to utilize it within a chart.

=SUM(INDIRECT(ADDRESS(117,Begin_Mo_History,,TRUE,"ISHistory")&":"&ADDRESS(117,End_Mo_History,,TRUE, ) ) )

Defined names: Begin_Mo_History = 5 (col E); End_Mo_History = 46 (col AT)

My goal is to reproduce the following range ('IS History'!$E$117:$AT$117) in a chart using the formula above. Can this be done?

<tbody>
</tbody>
 
Upvote 0
I have tried to use the INDEX function. I created the following function in Excel 2007, but an error message appeared when trying to add it as the Series Value in the Select Data Source box.
='IS History'!$E$117:INDEX('IS History'!$117:$117,COUNT('IS History'!$117:$117))
With values to chart in columns E through AZ of row 117, what should the formula be? My intent is to dynamically update the chart as new data is added.
 
Upvote 0
I can guess what the problem is but here's how you can learn for yourself.

A good way to learn what is wrong with a named formula is to enter it in a worksheet (not the name but the actual formula). Then, use the Formula Auditor to explore the problem.

So, with data in say E117:G117...
In A118:C118 array enter your formula. You should get an error. Now, select A118 and use the Formulas tab | Formula Auditing group | Evaluate Formula button.
I have tried to use the INDEX function. I created the following function in Excel 2007, but an error message appeared when trying to add it as the Series Value in the Select Data Source box.
='IS History'!$E$117:INDEX('IS History'!$117:$117,COUNT('IS History'!$117:$117))
With values to chart in columns E through AZ of row 117, what should the formula be? My intent is to dynamically update the chart as new data is added.
 
Upvote 0
As requested, the formula was keyed into A118. I made a slight adjustment to the formula to include 4 additional columns:
='IS History'!$E$117:INDEX('IS History'!117:117,(COUNT('IS History'!117:117)+4))


Using Evaluate Formula button, the following resulted:
Reference Evaluation
'IS History'!$A$118 $E$117:$AT$117

the final evaluation produced: #VALUE!

Can you tell from the above what I am missing to complete the formula for the charted variable?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top