Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    REPT function (2003 SP2)

    (The attachment is from Excel 2000 as I am posting away from the office)

    Good morning

    I use the REPT function to emulate a bar graph so that at a glance I can monitor performance (or lack of), please see attachment. Can anybody suggest an idea that would highlight say the 45th "I" (45 being the average number of calls required) by changing it to red or something like that.

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REPT function (2003 SP2)

    Steve,
    I do not know if this is exactly what you want, but have you considered using conditional formatting on the graph cells that will affect the format based on the length of the generated string?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REPT function (2003 SP2)

    You can't format part of the result of a formula. You'd have to use code to replace the formulas with their values and format those values.

    See attached version.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REPT function (2003 SP2)

    Hi Kieran

    Thanks but it is not actually a graph per se only a graphical representation of the preceeding cell by replacing the numerical value with a string of 'I''s to give a bar graph effect

    Cheers for the input

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REPT function (2003 SP2)

    Thanks very much Hans

    Off to have a play

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: REPT function (2003 SP2)

    You could also try this version - not a huge difference, but reasonably clear to me! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    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: REPT function (2003 SP2)

    Still another option is to modify Rory's example and use the formula:
    =REPT("",MIN(B4,44))&REPT("|",MAX(0,B4-44))

    This will have the smaller bars for the < average and use the bigger ones for anything more than average. This will make it stand out more.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REPT function (2003 SP2)

    Thanks both Rory and Steve

    Excellent and easy suggestions without code

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: REPT function (2003 SP2)

    Hi Steve

    Late for the show as usual but I was playing on a similar idea to Hans but got distracted, however, I have adapted his code to change the colour of the bars to everything to the right of the target value

    Sub EvaluateBarChart()
    Dim r As Long
    Dim n As Long
    n = 20
    For r = 2 To 7 ' adapt to fit length of column
    Range("C" & r) = Application.WorksheetFunction.Rept("I", Range("B" & r))
    Range("C" & r).Characters(n, Range("B" & r) - n + 1).Font.ColorIndex = 3
    Next r
    End Sub



    Maybe not for you as you wanted functions and not code but I thought I would tag it to the end of this for reference <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  10. #10
    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: REPT function (2003 SP2)

    Another option that gets more of exactly what you asked for is to use separate columns for the 3 segments...

    Steve
    Attached Files Attached Files

Posting Permissions

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