Graph: Sorting data into descending order, but including an average....

[lbickford]

I have graphs with information that I need to sort in descending order, but I would really like the graph to include and sort the averages too. I've attached my spreadsheet because I'm sure I'm not making any sense. Basically, there are 9 sections of the lab and they are ranked on their % of "Days in the Red" on certain measures - various overdue items - and I'd like the graph to include the "Lab Average," but I don't know how to make a cell that contains the lab average also be sortable. If I don't F4 it, the cells its averaging changes; if I do F4 it, it becomes circular. I really don't want to manually have to pick which cells to average each time; it was bad enough manually sorting them into descending order that I made a very silly and simple macro button. Feel free to laugh at my unsophisticated macro/button skills.

The first data group and graph in the example is Calibrations. Currently, there is one section at 30%, one at 10%, and seven sections at 0%, making the lab average 4%. I would love for the graph to be able to show me the 30% bar, the 10% bar, then the lab average bar of 4%, so that the other sections can easily see whether they are above or below the lab average. That's the goal of all of this.

Is there some way to F4 the area and exclude the Column B cell corresponding to the Column A cell containing the text "Lab Average"? Here's the formula language in my head: "Average cells B49:B57, except the cell in that range that has 'Lab Average' in the corresponding row in Column A"...it makes sense in my head....

Help!! Thank you!

[mahju]

You may try this

In column c, from C49 to C57 use average formula

C49:
=average($c$49:$c5$7) copied down to c57

Add this as second series in the graph and change it to line.
Remove average formula in column B and adjust the data

You should have a straight line showing average over bars.

I am unable to upload the file otherwise i will do it.

Thanks

[lbickford]

I'm not understanding First off, when I put that into c49, it's circular. And there's no data in the C column. Did you mean to say to put the averages of B49:B57 into the C column? The other problem is that I still need to be able to sort all the info into descending order, and if it's not all in a straight sortable row, I don't know how to sort it into descending order. The data changes daily, hence why I have to worry about sorting it.

[mahju]

I am sorry i cannot upload the file.

Please see these images for illustration

g1.JPG

G2.JPG

Thanks

[lbickford]

Ohh got it! Sorry, that was my bad for not understanding graphs well enough! I like this suggestion a lot! Thank you!

[mahju]

Thanks and glad that you like it.

Please mark the thread as solved if you think your problem is solved.

Regards

[lbickford]

Er...how do I do that?

[mahju]

click thread tools at top of your first post
select solved
Thanks

[lbickford]

Thanks for helping out a n00b!!