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

Jeff_Haskin · ‎Jul 29, 2022

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.

Hannah_Wiginton · ‎Jul 29, 2022

Awesome! Looks great.

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable

ScottWorld · ‎Jul 29, 2022

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

I love it!! :medal_military:

Fantastic job!!! :partying_face: :tada:

Kamille_Parks · ‎Jul 29, 2022

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 · ‎Jul 29, 2022

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

This is a powerhouse thread. Lol.

Bill_French · ‎Jul 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.

Olpy_Acaflo · ‎Jul 30, 2022

So do I @Bill.French ! :slightly_smiling_face:

selfquoted from your thread =