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

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.

6 Likes

Awesome! Looks great.

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

I love it!! :medal_military:

Fantastic job!!! :partying_face: :tada:

1 Like

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%.

3 Likes

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

This is a powerhouse thread. Lol.

1 Like

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. :wink:

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 =

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.