0

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): screenshot 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.

bdutta74
  • 2,798
  • 3
  • 31
  • 54
  • 1
    Please provide a data sample to illustrate what you're after. Really, with your rep you should know to ask better questions. – teylyn Mar 14 '21 at 19:43
  • Won’t you find those by taking the derivative? – Solar Mike Mar 14 '21 at 20:04
  • @teylyn, agreed, the question could certainly have been worded better. My reps are from a time and age when I was relatively smarter and younger. Let me try to update question with additional information. – bdutta74 Mar 15 '21 at 14:30
  • 1
    And you want to plot all that without having the data in the spreadsheet???? Why? My recommended approach: generate the data for the chart in the spreadsheet. Then you can see if there are any options for single formula calculations that do the same thing. And if you need help with the latter, then post the data sample as I said above. Your question is still not answerable with the info provided. – teylyn Mar 15 '21 at 19:08
  • In order to determine if/which formula can be used to optimise the chart source, we need to know what calculations you perform in your formulas to arrive at the data to be plotted. In short: If you want a formula, then you need to define what to calculate. I'm obviously having trouble making myself understood, so I'm out of here. – teylyn Mar 17 '21 at 01:03

1 Answers1

2

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.

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Firstly thanks for the answer. Clearly something I didn't know about Excel. Next, I suppose the fact that my price tiers are exponential, the linear stepping in the above mechanism might not work i.e. will require far too many samples to compute and chart out ? – bdutta74 Mar 15 '21 at 14:44
  • 1
    As I wrote above: If you can express the series data as an array formula, you can use it in the range name and the chart. So, your question should not be about how to chart this, but how to do the calculation of all the data points with a single formula. If that is not an option, for whatever reason, then you will need to use the worksheet as a data source, like any other chart does. – teylyn Mar 15 '21 at 19:06
  • Realized one keypoint about the above approach -- you need Office365 Excel for this. Unfortunately, this doesn't with Excel 2019 native desktop application, i.e. if SEQUENCE() is needed. I suppose it is a deadend for me then. – bdutta74 Mar 16 '21 at 04:54
  • 1
    Don't dismiss that so readily. Array formulas have been a part of Excel for the last 25 years. If you would just have the courtesy to post a data sample and explain the calculations you need, as I repeatedly asked, only then can we determine if it can be done or not. – teylyn Mar 16 '21 at 05:35
  • Updated my question with sample data in screenshot. Since the data was proprietary and confidential, I needed to massage it a bit before sharing something. In fact, even having shared it, I'm not so sure if this is what or how you wanted me to share. – bdutta74 Mar 16 '21 at 20:27