Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Chart Range ? (Excel 2003)

    IHi

    Steve kindly provided me with formulas to set the range for ABCD Customers.

    I would like to go a step further and set the List Price, Cost Price, and min Price ranges.

    The principle is to find the greatest number of Classes in this demo sheet there are 7 A, 4 B, 1 C, 1 D

    so the List Price range would be =Sheet1!$V$35:$V$41 but if therer were more B's say from I42 to I50 there would be more B's than A's so
    this would the have to the range =Sheet1!$V$42:$V$50

    Hope this is clear

    Many Thanks
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Range ? (Excel 2003)

    Don't create a whole list of them.

    You only need 2 points for a line set them based on:
    <table border=1><tr><td align=center valign=bottom>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Chart Range ? (Excel 2003)

    Hi Steve

    I apoligise if I seem a little dumb, but I can' t quite understand what you are suggesting here.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Range ? (Excel 2003)

    I was thinking like the attached. Since your three columns don't change there is not need to plot all the points, just the first and the last to make the line...

    You could move the table and delete those columns (any references to the value would have to change to the table itself)

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Chart Range ? (Excel 2003)

    Hi Steve

    Thanks for the explanation I can see how it works now.

    Not quite grasped what you mean by move the table and delete those columns,

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Range ? (Excel 2003)

    Change the formulas in V2:X2 to values.
    The formula in Y35 changes to:
    =($V$2-T35)/$V$2

    and is copied down. Any other cells (some formulas you have converted to values, presumably to make the file smaller) that refer to these 3 columns need to change to the values in row 2or 3 of the those columns.

    Then, unless you need cols for VWX for anything else, you do not need to have them at all. You can move the table (U1:X3) to Y1 and then delete cols VWX.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Chart Range ? (Excel 2003)

    Hi Steve

    Thanks for the reply, but I am a bit baffled because I don't have a formula in Y35??.

    Many thanks Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart Range ? (Excel 2003)

    You have 1 in Y46. I presumed, at some point it went from Y35:Y47 and (for some reason) this one did not get converted to a value at some point.

    If you have no other calculations based on columns, V,W,X, you can just move the table, convert Y46 and then delete cols V, W, X.

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Chart Range ? (Excel 2003)

    Hi Steve

    Thanks for that I didn't notice that as I just dumped the data across and pay much attention to it. I can understand your reasoning now.

    Thanks very much. Your help on this has been extremely useful.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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