Using Excel is it possible to plot a graph without explicitly generating table data, but using only a formula?

Question

Have a complex formula in Excel, where in currently I fill in values manually in a cell, and the result of applying that formula are available in another cell, few rows below the input data cell. Was wondering if there is a way to apply automatically, a range of values to the input cell, while plotting the output ? What I am trying to do is identify the inflection points in the graph.

Nature of data is tiered product pricing information, which is being taken through a series of transformations, s.a.

• currency conversion,
• country markup,
• volume discounts
• etc.

I am trying to identify the points in curve where there is a sudden jump (perhaps calling it "inflection point" is mathematically incorrect), since the transformations cause such points to move. The formula applies some logical operations (if this then that or else something else), some VLOOKUPS of price from a sorted pricing table, country markup table, currency conversion factors etc. In short, it is not a set of plain arithmetic operations.

To make things harder, the pricing tiers are linked to volume with an exponential relationship. Something like:

• Tier-1: Qty <= 100
• Tier-2: Qty 101 to 1000
• Tier-3: Qty 1001 to 10K
• Tier-4: Qty 10001 to 100K
• Tier-5: Qty 100001 to 1M
• etc.

For now, here is how I manage this (including sample data): Row#11-18 are a series of formulas i.e. each successive row contains an arithmetic, logical, VLOOKUP using cell above, or on tables D2:F7 or H2:J7.

Answer 1

You can use named ranges to define a chart series. Use an array formula that produces an array of values and plug the named range into the chart. You need to use the workbook name or the sheet name before the range name in the data source dialog. See screenshot.