# Thread: REPT function (2003 SP2)

1. ## 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

2. ## 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. ## 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.

4. ## 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

5. ## Re: REPT function (2003 SP2)

Thanks very much Hans

Off to have a play

Cheers

Steve

6. ## 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>

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

Thanks both Rory and Steve

Excellent and easy suggestions without code

Cheers

Steve

9. ## 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>

10. ## 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

#### Posting Permissions

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