Is it possible to create a 3D Clustered Column Chart

[Yuko]

Hello All,

I'm trying to create a 3D Clustered Column Chart but can't find any tutorial for this.

Has somebody any idea how to do this??

My goal is to simulate the loading of a container or truck with boxes.

Yuko

[TMS]

Right click on the chart you have. Select Change Chart Type, then pick 3D Clustered Chart.

[Yuko]

Thank you TMS,

maybe I misphrased the meaning by using the word clustered, because I want to see the column in 2 to 5 parts, each part representing a box.

Like I can stack 2 x a box of 102 cm high + 1 x box with a height of 58 cm .
Maybe I need to give each dimension a different color so I cansee which boxes are where located in the truck or container.

Yuko

[TMS]

In all honesty, I'm struggling to understand what you are trying to achieve. The data you have put into the chart is very limited so it is difficult to imagine what you expect/need the output to look like.

I suggest that you put more data into the chart area so that the chart is fuller. Then right click on it, change the chart type and explore what the different types/options look like.

[Yuko]

TMS,


the idea is that each column is representing a stack of boxes, in a truck or container.
Sometimes a stack can consist of 2 to 5 boxes and we can load 3 stacks on 1 row in a truck or container.
So I want to see a column that is divided in 2 to 5 parts, depending on which size of box is in the stack.
Sometimes we load only all the same sized boxes, but it can happen that in 1 stack we have 3 different boxes.
All depends on the quantity and the sizes of the boxes and how efficient we load the truck or container.
And I want to simulate with my chart a truck trailer or a container.
The 3 stacks are putted in the width of the container and the 18 rows that you see are the rows in the lenght
of the container. With 18 rows the container is full.

Yuko

[MrShorty]

That sounds like a 3D stacked column chart. I don't see this chart type available in Excel, and I am unaware of any online tutorials. A quick internet search found a reference to John Walkenbach's book (Excel Charts) that shows how to fake a 3D stacked column chart by tinkering around with multiple 3D column charts. Commentary with the reference suggests that it is a real irritating procedure to make and maintain. The reference to the book was old (this thread: https://www.mrexcel.com/forum/excel-...tml#post835598 ), and I don't know which edition (if there are multiple editions available) or anything about finding the tutorial Walkenbach apparently has.

Have you searched for other charting applications that might do this? My guess is that, if someone has a charting application or graphics application that can do this, that will be the easiest approach. If you must use Excel for this, I would suggest you find the right copy of Walkenbach's book, and follow his instructions.

[Yuko]

Thank you MrShorty,


this means I need to find another way to get a solution for my question, maybe using Excell for doing the calculation and a graphical or 3D drawing tool to create the visualisation of the result.

Yuko

[MrShorty]

It seems that most charting applications will have ways of "talking" to Excel to get data, so it certainly seems possible to do the calculation in Excel and use a different application for the visualization. Of course, some of those applications can also do the calculations -- and Excel would not be needed at all. An internet search for "3D stacked bar chart" found a few different applications that might be able to do do this -- including one that suggested that Matlab might be able to do this, which would have plenty of computation capability.

Good luck with your search. If you find something good, maybe let us know what you find for future reference.

[Andy Pope]

It's either Clustered in Stacked charts in excel.

You have to do tricks in order to combine both styles. Bad enough in 2d.
In 3d you are limited even further as you can not combine chart types or use multiple axes.

With VBA it's possible to create the illusion using gradient fill. But I would not suggest you use this approach.

Given the boxes can vary in size in 3 dimensions maybe VBA and shapes are the best way forward if you can not find more appropriate software.

[Yuko]

Mr Shorty,

I prepared in the Word document a bit how I see a possible solution on my problem.
In the Excell document I tried to explain in 4 steps how I see it working.
Only I don't know how to write the formulas to make it working.
Do you think you can help me out whit this problem or do you say it is impossible what I'm asking.

Yuko

[MrShorty]

Short but useless answer -- yes I think this is something that can work in Excel. What you describe looks like a fairly standard "knapsack problem", and people have been using Excel for years to solve knapsack problems (https://en.wikipedia.org/wiki/Knapsack_problem ). I am not experienced with these, but here are a couple of pages to refer to:

From the makers of Excel's Solver utility: https://www.solver.com/optimization-...stics-examples
Someone's blog entry: http://blog.excelmasterseries.com/20...el-solver.html

This forum's search engine is not the greatest, but I know this is not the first knapsack type problem to have come across this forum. You might also search this forum.

[Yuko]

Thanks a lot Mr Shorty,

for this info and a lot of new stuff to read and study.

But it seems more complicated than I supposed to make something I do daily manually a bit more automated.

Anyhow thanks a lot for your efforts.

Yuko