Video Discription |
Star rating systems are an effective visualisation tool for making snap judgements about data.
Although conditional formatting can achieve one, your options are limited.
This formula-based method gives you complete control over the stars' breakpoints, so they can kick in disproportionately if desired. This is ideal for suiting the kind of data you're working with. Moreover, half-stars are also used to add extra precision.
In the movies table, the Rating column displays a number out of 100 for each.
How do we convert these figures into something more aesthetically pleasing?
This formula does the trick:
=๐ป๐ด๐(
๐ต๐๐๐๐๐๐๐,
๐๐๐ธ๐๐ฒ๐ท(๐๐๐๐ด,
[@๐๐๐๐๐๐]๏ผ=๐ฟ0,๐ป,
[@๐๐๐๐๐๐]๏ผ=๐ฝ๐ป,๐บ,
[@๐๐๐๐๐๐]๏ผ=๐ป0,๐น,
[@๐๐๐๐๐๐]๏ผ=๐บ0,๐ธ,
[@๐๐๐๐๐๐]๏ผ=๐น0,๐ท,
[@๐๐๐๐๐๐]๏ผ๐น0,0
),
๐ท๐๐๐๐๐๐๐,
๐ธ๐ต(
๐๐๐ธ๐๐ฒ๐ท(
๐ต๐๐๐๐๐๐๐,
๐ป,[@๐๐๐๐๐๐]๏ผ๐ฟ0,
๐บ,[@๐๐๐๐๐๐]๏ผ=๐พ๐ป,
๐น,[@๐๐๐๐๐๐]๏ผ=๐ผ๐ป,
๐ธ,[@๐๐๐๐๐๐]๏ผ=๐บ๐ป,
๐ท,[@๐๐๐๐๐๐]๏ผ=๐น๐ป,
0,[@๐๐๐๐๐๐]๏ผ=๐ท๐ป),
"ยฝ",),
๐๐ด๐ฟ๐("๐ท",๐ต๐๐๐๐๐๐๐)&๐ท๐๐๐๐๐๐๐
)
*๐๐ถ๐ฆ ๐ต๐ฐ ๐ค๐ฐ๐ฎ๐ฑ๐ข๐ต๐ช๐ฃ๐ช๐ญ๐ช๐ต๐บ, ยฝ (๐ฉ๐ข๐ญ๐ง-๐ด๐ต๐ข๐ณ) ๐ข๐ฏ๐ฅ 1 (๐ง๐ถ๐ญ๐ญ-๐ด๐ต๐ข๐ณ) ๐ข๐ณ๐ฆ ๐ถ๐ด๐ฆ๐ฅ ๐ช๐ฏ๐ด๐ต๐ฆ๐ข๐ฅ ๐ฐ๐ง ๐ต๐ฉ๐ฆ๐ช๐ณ ๐ณ๐ฆ๐ด๐ฑ๐ฆ๐ค๐ต๐ช๐ท๐ฆ ๐ด๐บ๐ฎ๐ฃ๐ฐ๐ญ๐ด.
Let's use The Matrix โ 73 as an example.
The LET function stores two named values: FullStar and HalfStar.
FullStar uses a SWITCH statement to list a set of thresholds. The first condition it passes is the third โ 73 is greater than or equal to 50. Therefore, 3 is returned.
HalfStar then uses the value of FullStar to jump to 3 in its own SWITCH statement and determine whether 73 is greater than or equal to 65. It is, so IF's [๐๐๐๐๐_๐๐_๐๐๐๐] is triggered to return a half-star.
REPT then repeats the full star three times and concatenates it to the half star.
The star symbols are found in Insert ๏ผ Symbols ๏ผ Symbol. Set the font to Segoe MDL2 Assets and double-click on one to insert it. Be aware they appear strangely in the formula bar because they aren't official Unicode symbols.
You don't have to use stars, however!
Finally, you can also change their colour using the Font Color dropdown.
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest virtual gathering of Microsoft Excel users and experts.
Learn more at https://globalexcelsummit.com/.
Find us on:
๐ LinkedIn: https://www.linkedin.com/showcase/global-excel-summit
๐ค Twitter: https://twitter.com/ExcelSummit
๐ฆ Facebook: https://www.facebook.com/globalexcelsummit
๐ธ Instagram: https://www.instagram.com/globalexcelsummit
๐คณ TikTok: https://www.tiktok.com/@globalexcelsummit [Us7_X42MurQ] |