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