Skip to main content

Make an in-line histogram! Copy/paste this formula

J
Forum|alt.badge.img+9

I figured out how to make a histogram inline with the table and I thought I’d share:

It was horribly tedious for me but it won’t be for you!

This formula expects whole-number values between 0 and 100. Here’s the formula:

CONCATENATE(
  SWITCH(
    ROUND({1}/100,2),
    0.00,"_.",
    0.01,"▁",0.02,"▁",0.03,"▁",0.04,"▁",0.05,"▁",0.06,"▁",
    0.07,"▁",0.08,"▁",0.09,"▁",0.10,"▁",0.11,"▁",0.12,"▁",
    0.13,"▂",0.14,"▂",0.15,"▂",0.16,"▂",0.17,"▂",0.18,"▂",
    0.19,"▂",0.20,"▂",0.21,"▂",0.22,"▂",0.23,"▂",0.24,"▂",
    0.25,"▃",0.26,"▃",0.27,"▃",0.28,"▃",0.29,"▃",0.30,"▃",
    0.31,"▃",0.32,"▃",0.33,"▃",0.34,"▃",0.35,"▃",0.36,"▃",
    0.37,"▄",0.38,"▄",0.39,"▄",0.40,"▄",0.41,"▄",0.42,"▄",
    0.43,"▄",0.44,"▄",0.45,"▄",0.46,"▄",0.47,"▄",0.48,"▄",0.49,"▄",
    0.50,"▅",0.51,"▅",0.52,"▅",0.53,"▅",0.54,"▅",0.55,"▅",
    0.56,"▅",0.57,"▅",0.58,"▅",0.59,"▅",0.60,"▅",0.61,"▅",0.62,"▅",
    0.63,"▆",0.64,"▆",0.65,"▆",0.66,"▆",0.67,"▆",0.68,"▆",
    0.69,"▆",0.70,"▆",0.71,"▆",0.72,"▆",0.73,"▆",0.74,"▆",
    0.75,"▇",0.76,"▇",0.77,"▇",0.78,"▇",0.79,"▇",0.80,"▇",
    0.81,"▇",0.82,"▇",0.83,"▇",0.84,"▇",0.85,"▇",0.86,"▇",0.87,"▇",
    0.88,"█",0.89,"█",0.90,"█",0.91,"█",0.92,"█",0.93,"█",
    0.94,"█",0.95,"█",0.96,"█",0.97,"█",0.98,"█",0.99,"█",1.00,"█"
  ),
  SWITCH(
    ROUND({2}/100,2),
    0.00,"_.",
    0.01,"▁",0.02,"▁",0.03,"▁",0.04,"▁",0.05,"▁",0.06,"▁",
    0.07,"▁",0.08,"▁",0.09,"▁",0.10,"▁",0.11,"▁",0.12,"▁",
    0.13,"▂",0.14,"▂",0.15,"▂",0.16,"▂",0.17,"▂",0.18,"▂",
    0.19,"▂",0.20,"▂",0.21,"▂",0.22,"▂",0.23,"▂",0.24,"▂",
    0.25,"▃",0.26,"▃",0.27,"▃",0.28,"▃",0.29,"▃",0.30,"▃",
    0.31,"▃",0.32,"▃",0.33,"▃",0.34,"▃",0.35,"▃",0.36,"▃",
    0.37,"▄",0.38,"▄",0.39,"▄",0.40,"▄",0.41,"▄",0.42,"▄",
    0.43,"▄",0.44,"▄",0.45,"▄",0.46,"▄",0.47,"▄",0.48,"▄",0.49,"▄",
    0.50,"▅",0.51,"▅",0.52,"▅",0.53,"▅",0.54,"▅",0.55,"▅",
    0.56,"▅",0.57,"▅",0.58,"▅",0.59,"▅",0.60,"▅",0.61,"▅",0.62,"▅",
    0.63,"▆",0.64,"▆",0.65,"▆",0.66,"▆",0.67,"▆",0.68,"▆",
    0.69,"▆",0.70,"▆",0.71,"▆",0.72,"▆",0.73,"▆",0.74,"▆",
    0.75,"▇",0.76,"▇",0.77,"▇",0.78,"▇",0.79,"▇",0.80,"▇",
    0.81,"▇",0.82,"▇",0.83,"▇",0.84,"▇",0.85,"▇",0.86,"▇",0.87,"▇",
    0.88,"█",0.89,"█",0.90,"█",0.91,"█",0.92,"█",0.93,"█",
    0.94,"█",0.95,"█",0.96,"█",0.97,"█",0.98,"█",0.99,"█",1.00,"█"
  ),
  SWITCH(
    ROUND({3}/100,2),
    0.00,"_.",
    0.01,"▁",0.02,"▁",0.03,"▁",0.04,"▁",0.05,"▁",0.06,"▁",
    0.07,"▁",0.08,"▁",0.09,"▁",0.10,"▁",0.11,"▁",0.12,"▁",
    0.13,"▂",0.14,"▂",0.15,"▂",0.16,"▂",0.17,"▂",0.18,"▂",
    0.19,"▂",0.20,"▂",0.21,"▂",0.22,"▂",0.23,"▂",0.24,"▂",
    0.25,"▃",0.26,"▃",0.27,"▃",0.28,"▃",0.29,"▃",0.30,"▃",
    0.31,"▃",0.32,"▃",0.33,"▃",0.34,"▃",0.35,"▃",0.36,"▃",
    0.37,"▄",0.38,"▄",0.39,"▄",0.40,"▄",0.41,"▄",0.42,"▄",
    0.43,"▄",0.44,"▄",0.45,"▄",0.46,"▄",0.47,"▄",0.48,"▄",0.49,"▄",
    0.50,"▅",0.51,"▅",0.52,"▅",0.53,"▅",0.54,"▅",0.55,"▅",
    0.56,"▅",0.57,"▅",0.58,"▅",0.59,"▅",0.60,"▅",0.61,"▅",0.62,"▅",
    0.63,"▆",0.64,"▆",0.65,"▆",0.66,"▆",0.67,"▆",0.68,"▆",
    0.69,"▆",0.70,"▆",0.71,"▆",0.72,"▆",0.73,"▆",0.74,"▆",
    0.75,"▇",0.76,"▇",0.77,"▇",0.78,"▇",0.79,"▇",0.80,"▇",
    0.81,"▇",0.82,"▇",0.83,"▇",0.84,"▇",0.85,"▇",0.86,"▇",0.87,"▇",
    0.88,"█",0.89,"█",0.90,"█",0.91,"█",0.92,"█",0.93,"█",
    0.94,"█",0.95,"█",0.96,"█",0.97,"█",0.98,"█",0.99,"█",1.00,"█"
  )
)

Just replace the field name ( {1} , {2} , and {3} in this example ) with your actual field names, and repeat the Switch formula for each field you want included in your switch formula.

SWITCH() only directly compares specific values, it won’t let you compare the value in a field to a range like 50<{fieldName}<60. So, I had to create for every 2-digit decimal between 0 and 1 to make it work.

Sam_Hancock
M
J
6 people like this
  • Sam_Hancock
    Sam_Hancock
  • M
    Matthew_Whalley
  • J
    Jonathan_Goldma
  • W
    WouterJ
  • J
    julien1
  • F
    fpalomo

6 replies

Hannah_Wiginton
Forum|alt.badge.img+18

Awesome! Looks great.

---Hannah - On2Air.com - Automated Backups for Airtable
J
1 person likes this
  • J
    Jeff_Haskin
ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8779 replies
  • July 29, 2022

Holy cow, that’s amazing!! :grinning_face_with_big_eyes: :raised_hands:

I love it!! :medal_military:

Fantastic job!!! :partying_face: :tada:

Hannah_Wiginton
J
2 people like this
  • Hannah_Wiginton
    Hannah_Wiginton
  • J
    Jeff_Haskin
Kamille_Parks11
Forum|alt.badge.img+25

Excellent, Jeff!

Looking at your implementation, you’re displaying the histograms with the “eighths” character blocks. With that in mind, you may be able to adjust your rounding a bit differently to divide into eighths. Doing so shortens up the formula a fair bit. (Formula below also assumes whole-numbers between 0 and 100)

CONCATENATE(
  SWITCH(
    ROUNDDOWN({1} / 12.5, 0),
    0,"_.",1,"▁",2,"▂",3,"▃",4,"▄",
    5,"▅",6,"▆",7,"▇",8,"█"
  ),
  SWITCH(
    ROUNDDOWN({2} / 12.5, 0),
    0,"_.",1,"▁",2,"▂",3,"▃",4,"▄",
    5,"▅",6,"▆",7,"▇",8,"█"
  ),
  SWITCH(
    ROUNDDOWN({3} / 12.5, 0),
    0,"_.",1,"▁",2,"▂",3,"▃",4,"▄",
    5,"▅",6,"▆",7,"▇",8,"█"
  )
)

If the anticipated values are positive values, but not necessarily 0 - 100 or integers, then you could replace the ROUNDOWN function with:

ROUNDDOWN(ROUND({1}/{Max Value}, 2)/0.125, 0)

^ that will first get the value of the {1} field as a percentage, then will return a whole number 0 through 8 based on that percentage to control the rest of the SWITCH function. It does not account for values greater than 8, but you could use the default property of the SWITCH function to put in “█” if the percentage is, say, 110%.

ScottWorld
J
J
5 people like this
  • ScottWorld
    ScottWorld
  • J
    Jean_Thoensen
  • J
    Jeff_Haskin
  • kuovonne
    kuovonne
  • O
    Olpy_Acaflo
ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8779 replies
  • July 29, 2022
Kamille_Parks11 wrote:

Excellent, Jeff!

Looking at your implementation, you’re displaying the histograms with the “eighths” character blocks. With that in mind, you may be able to adjust your rounding a bit differently to divide into eighths. Doing so shortens up the formula a fair bit. (Formula below also assumes whole-numbers between 0 and 100)

CONCATENATE(
  SWITCH(
    ROUNDDOWN({1} / 12.5, 0),
    0,"_.",1,"▁",2,"▂",3,"▃",4,"▄",
    5,"▅",6,"▆",7,"▇",8,"█"
  ),
  SWITCH(
    ROUNDDOWN({2} / 12.5, 0),
    0,"_.",1,"▁",2,"▂",3,"▃",4,"▄",
    5,"▅",6,"▆",7,"▇",8,"█"
  ),
  SWITCH(
    ROUNDDOWN({3} / 12.5, 0),
    0,"_.",1,"▁",2,"▂",3,"▃",4,"▄",
    5,"▅",6,"▆",7,"▇",8,"█"
  )
)

If the anticipated values are positive values, but not necessarily 0 - 100 or integers, then you could replace the ROUNDOWN function with:

ROUNDDOWN(ROUND({1}/{Max Value}, 2)/0.125, 0)

^ that will first get the value of the {1} field as a percentage, then will return a whole number 0 through 8 based on that percentage to control the rest of the SWITCH function. It does not account for values greater than 8, but you could use the default property of the SWITCH function to put in “█” if the percentage is, say, 110%.


Fantastic catch & significant enhancement, @Kamille_Parks!! :smiling_face_with_sunglasses: :clap:

This is a powerhouse thread. Lol.

Hannah_Wiginton
J
2 people like this
  • Hannah_Wiginton
    Hannah_Wiginton
  • J
    Jeff_Haskin
B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3264 replies
  • July 30, 2022

Also known as a sparkline. Clever use of the character set and formulas. It’s a hack, but I mean that in an admirable way. :winking_face:

I wish Airtable would support the ability to render a URL field like these (below). Then, sparklines of many types and from any service could be injected as visual objects based on complex data sources and relationships.

J
1 person likes this
  • J
    Jeff_Haskin
O
Forum|alt.badge.img+18
  • Olpy_Acaflo
  • Inspiring
  • 251 replies
  • July 30, 2022
Bill_French wrote:

Also known as a sparkline. Clever use of the character set and formulas. It’s a hack, but I mean that in an admirable way. :winking_face:

I wish Airtable would support the ability to render a URL field like these (below). Then, sparklines of many types and from any service could be injected as visual objects based on complex data sources and relationships.


So do I @Bill.French ! :slightly_smiling_face:

selfquoted from your thread =

Reply


Most helpful members this week

Terms & Conditions