We previously looked at adding a rating icon using the smiley faces available in the Wingdings font. You can also quickly create a thermometer rating by using the ReplicateString function.
The Replicate String Function
The ReplicateString function lets you define a string to repeat, and the number of times to repeat it. The power of the function is that the number of times to repeat the string can be a formula.
ReplicateString takes the following syntax:
|String||The String you want repeated.|
|#copies||A whole number indicating the number of times to repeat String.|
Make Your Thermometer with ReplicateString
- Create a new formula, with an appropriate name (for example, Thermometer) that defines how many times to repeat the character you want to show. In our case, we’re going to use a lower case “L” (which looks like a bar), and draw one bar for each 200 units that are sold. (So, a 200 would have 1 bar, an 800 would have 4, and so on.)
(The Round function is necessary because the result of your division must be a whole number.)
- Place this new formula in the appropriate place in your report.
- Check how it looks:
A Rating Thermometer
Another use for the ReplicateString function is to add a rating thermometer, like the one you might see on movie reviews. Combining a formula with the WingDings font (the letter “C” is a thumbs-up) lets you give movies a rating of 0 to five thumbs up.
- Create a formula that will show the correct number of thumbs up icons. Because our database only allows users to give a whole number rating, we can use the following formula:
- Place the formula in the correct location on your report. Here’s how it looks:
Of course, not everything is this neat. What if you want to add up several ratings for the same movie and give the average? Just modify your formula to include the Round function like we did in the previous example.