ADOBE AFTER EFFECTS: Forum Expressions Tutorials Creative Cloud

# create guage that displays output from list of numbers (excel or text list comma delimited)

FAQ   •   VIEW ALL
 create guage that displays output from list of numbers (excel or text list comma delimited) on Aug 20, 2014 at 6:07:15 pm

I want to take a list of numbers (from excel) and use them, one per frame, to animate a gauge (like a single bar graph) going up and down depending on the number, as a percentage of the maximum number.

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 20, 2014 at 8:42:59 pm

Create a text layer, and paste your comma-delimited list of numbers into it. This particular expression does not do very robust error-checking, so make sure you've formatted it correctly -- just numbers and commas.

Add a solid to your comp and scale it however you like to be the shape of the gauge. Set the anchor point to the bottom of the solid (or wherever you'd like it to be).

Apply the following expression to the scale property of the solid:

```// expression to take input of series of numerical values and use it to adjust scale as a percentage based on these values, one per frame // get our source list listText = thisComp.layer("Comma-Delimited Text Input").text.sourceText; // now make an array, assuming a comma delimiter list = listText.split(","); // what's the biggest value? we'll use this as our denominator maximum = Math.max.apply(Math, list); // which number should we look at? one per frame for this layer target = timeToFrames(time-inPoint); // make sure we don't try to find the value-as-a-percentage for an out-of-bounds array number, or try to divide by zero if ((target >= 0) && (target < list.length) && (maximum != 0)) { // the percentage is the current value divided by the maximum value percentage = list[target] / maximum; } else percentage = 0; // now return as scale [value[0],value[1]*percentage]```

Walter Soyka
Motion Graphics, Widescreen Events, Presentation Design, and Consulting

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 21, 2014 at 12:31:44 pm

Bam! It works. I knew there was a way. Thanks for the input. Lifesaver!!!

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 21, 2014 at 1:25:02 pm

Bam! It works! I knew there was a way. Thanks for the input. Lifesaver!!!

To further the challenge... The data I have is plotted once per second. Is there a way to accomplish this?

Thanks again.

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 21, 2014 at 2:11:03 pm

[chris gulliford] "To further the challenge... The data I have is plotted once per second. Is there a way to accomplish this?"

Change this line:
```// which number should we look at? one per frame for this layer target = timeToFrames(time-inPoint);```

To this:
```// which number should we look at? one per second for this layer target = Math.floor(time-inPoint);```

Walter Soyka
Motion Graphics, Widescreen Events, Presentation Design, and Consulting

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 21, 2014 at 2:45:39 pm

Again, much thanks! Geneous!!

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 29, 2014 at 1:36:49 am

That is a cool technique Sir Soyka.

I have a similar issue, but in my case my data in Excel is not in regular intervals, my data points come at sporadic frames.

After much searching on the webz, I wasn't finding much on bringing data from Excel to AE, except for things you can pay for. Then I had a random thought, I selected some keyframes in AE, ctrl-c, went to Excel, ctrl-v. Badda Bam!

This gives you the format (which depends on what sort of keyframes you are copying), then you can populate as you need, select and copy in Excel, go to AE, and paste.

BAM!!

The only hitch (or maybe it's a benefit) is AE will paste your first keyframe where ever your time marker is. So if you want Frame 137 in Excel to be Frame 137 in AE, make sure your first Frame in Excel is 0, and you are on Frame 0 in AE when you paste.

In my case, I created a Null layer, added a bunch of Slider Controls under Effects/Expression Controls, and pasted all my data onto them. Then I used various expressions to convert the data to work with each gauge.

Up yours expensive data importing products!!!
Oh fine, they're not even that pricey, but up yours!

 Re: create guage that displays output from list of numbers (excel or text list comma delimited)on Aug 29, 2014 at 1:44:08 pm

Yes, Ae's keyframes-as-clipboard-text can be very powerful -- this lets you pretty easily massage data in Excel, too. The only downside is that the format is highly specific and doesn't carry over keyframe interpolation information.

But on the plus side, don't forget that keyframes can work in conjunction with an expression on the layer; you can paste the data in from Excel and then write an expression that handles the interpolation however you see fit.

Walter Soyka