+ Reply to Thread
Results 1 to 7 of 7

Multiply a series values 'on the fly' before presenting in chart.

  1. #1
    Registered User
    Join Date
    06-04-2014
    Posts
    3

    Multiply a series values 'on the fly' before presenting in chart.

    I would like to multiply a serie in 'series values' (before presenting on a chart) with a factor (in a formula like ='[1]name'!$V$2:$V$148)

    in other words I would like to do something like
    =('[1]name'!$V$2:$V$148)*50

    but I don't seem to succeed with the correct syntax.

    In Excel Help searching for "multiply series values" under "edit series" there is to read:
    "Use this option to include additional data series on the chart or to modify the name and values of existing data series without affecting the data on the worksheet."

    So what I want to do seems to be possible. What am I doing wrong?

    Excel 2007 version 12.0.6611.1000

    I know I can create a temporary column with the multiplied values but THIS IS NOT the way I want to solve the problem.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Multiply a series values 'on the fly' before presenting in chart.

    You would need to create a named range and then change the chart series formula to use that named range rather than cell reference.

    In the attached example you can change the multiplication factor in A1.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,444

    Re: Multiply a series values 'on the fly' before presenting in chart.

    Hello,

    I would do this using named ranges.

    Create a named range called "data" that refers to ='[1]name'!$V$2:$V$148

    Create a second named range called "modified_data" that refers to =data*50

    Use the named range "modified_data" as the source data for your chart.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Registered User
    Join Date
    06-04-2014
    Posts
    3

    Re: Multiply a series values 'on the fly' before presenting in chart.

    Thanks to both Andy Pope and sweep, your solutions work fine and are basically what I have been using: a manipulated temporary 'man in the middle' column.

    This is not what I want to use. I want to manipulate directly the series and multiply it 'on the fly' without doing an extra stop on the temporary column.

    The chart is built up by the SERIES formula that in my case looks like this:

    =SERIES('[Book2]stq day'!$T$1;'[Book2]stq day'!$A$2:$A$148;'[Book2]stq day'!$T$2:$T$148;1)

    and what is seen by clicking "edit" in "select data" are the parameters of the SERIES formula.
    So what I wish to do is to multiply the cell references in the SERIES formula by a random number.

    As far as I can read Excel Helps states that this can be done.

    Syntax? Suggestions?

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Multiply a series values 'on the fly' before presenting in chart.

    I will stick my neck out and say no you can not do that.

    The charts series formula is not like a worksheet formula.

  6. #6
    Registered User
    Join Date
    06-04-2014
    Posts
    3

    Re: Multiply a series values 'on the fly' before presenting in chart.

    wrong post
    Last edited by luftgekühlt; 06-05-2014 at 02:19 AM.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Multiply a series values 'on the fly' before presenting in chart.

    I agree with Andy (I'm not sure what's wrong with the suggested method). I think you are misinterpreting the Help,which could be clearer - a SERIES formula can include literal values rather than ranges and you can alter those values.
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Chart Series With Zero Values - How to ommit from chart
    By Maroota in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-21-2014, 05:55 AM
  2. Omiting a series of zero values from a chart
    By Tigger01 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-28-2012, 07:30 AM
  3. How to Chart multiply values on x axis
    By mswltd in forum Excel General
    Replies: 1
    Last Post: 09-27-2010, 11:15 AM
  4. Change Chart Series values in VBA
    By testar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2006, 12:26 PM
  5. presenting lots of data in a chart & table
    By tee in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-30-2005, 07:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1