7 Replies Latest reply on Oct 14, 2016 12:06 PM by Shinichiro Murakami

# Weighted Average of Dimensions?

Hi All -

I have a few bar charts that I've created based on bins of data. For example, I've bucketed the year companies have been founded into "0-2 years, 3-5 years, 6-9 years, +10 years" to represent age of a company. Now, I need to figure out what the average age of a company is. When I pull in a reference line, it just shows me the average based on the y-axis but i actually need a vertical average that pulls from the bins. I tried writing a few variations of weighted average formulas I saw from other posts but I think the way my data is structured doesn't work with those solutions. Confusing to explain so I've attached a workbook to explain. Looking at the tab labeled "Age", but need similar formula for # Employees and Funding as well!

Thanks so much,

Rajul

• ###### 1. Re: Weighted Average of Dimensions?

Talking about age, why don't you use formula like

[Age 2]

2016-int([CP - Year founded])

Talking about display, you can not add ref line on discrete axis, so prepare something like below and add to label.

[Fixed Age]

if attr([Age])= "6-10 Years"

then "Average Age= "+str(attr(round({fixed :avg([Age 2])},0)))

end

Thanks,

Shin

• ###### 2. Re: Weighted Average of Dimensions?

Hi Shin -

Thanks so much for your help. This doesnt make sense to me though... how can the average be 9? Shouldn't the average age of all the companies be probably between 2-3?

Also, I'm not clear on the formula you created for Age 2. Why do we reference "6-10 years"? And is there any way to make the average mark a vertical line?

Thanks,

Rajul

• ###### 3. Re: Weighted Average of Dimensions?

Reference line requires continuous axis, so you can not draw it with reference line on this format..

You can check raw data, but high average is driven by a few extreme numbers.

The reason I pick "6-10 years"? is it's around the center, you can remove that part, then you see the label on top of all the bars.

Thanks,

Shin

• ###### 4. Re: Weighted Average of Dimensions?

Got it! Checked the data and you are correct. That makes sense!

Do you know how we can tweak the formula to remove these large values (outliers) that are skewing the average?

• ###### 5. Re: Weighted Average of Dimensions?

One example with excluding >20, you can change the criteria as you like.

[Fixed Age]

"Average Age= "+str(attr(round({fixed :avg(if [Age 2]<20 then [Age 2] end)},0)))

Thanks,

Shin

• ###### 6. Re: Weighted Average of Dimensions?

This is so helpful, Shin! Thank you so much, it works.

Last question related to this topic - I got these formulas to work for Avg Age and Avg # Employees but the Funding one is a little tricky because there is no measure tied to it and the raw data is in buckets already.

How can I apply what we just went through to find an Avg Funding? It won't be a discrete number but a bucket that most companies fall into.

• ###### 7. Re: Weighted Average of Dimensions?

No idea. it's anyways, not correct if you don't have raw data.

BTW, could you mark my answer as correct.

Thanks,

Shin