Help

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

1066 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeff_Haskin
6 - Interface Innovator
6 - Interface Innovator

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

Screenshot from 2022-07-29 12-51-22

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 Replies 6

Awesome! Looks great.

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable

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

I love it!! :medal_military:

Fantastic job!!! :partying_face: :tada:

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.

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.

image

So do I @Bill.French ! :slightly_smiling_face:

selfquoted from your thread =