• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Locate associated chart data - Reverse Engineer

Jill.Pleau

New Member
I have been tasked to update the attached data / charts from a few years ago, created by someone who has left the company. I am not an expert on charts - but usually know enough to reverse engineer previously developed solutions; I cannot understand a few things about these charts.

I will typically not limit myself to a 'range' (mainly because I don't fully understand them), but I suspect the range may hold the answer to these questions.

my questions; on Pivot OMMT1 for instance;

1. The red target line and percentage above (the line represents machine capacity - I suspect the % is utilization, are those hand typed)
1A. Where is this data located?
2. The green target bars (I suspect that this data is coming from the BULL tab, column M - which represents available manpower)
2A. How is the chart designed to include this data - or is there something else that I am missing?

Any advice would be greatly appreciated. Regards, Jill
 
vletm,

I had attached a file, but perhaps didn't attach it in the correct way. I have attached another. I have had word on another site that the data in question was manually added. I am now working on HOW to add these features so that they are automatic. Thank you for your reply, it's much appreciated. Jill
 

Attachments

  • Capacity.xlsx
    807.7 KB · Views: 6
vtlem,

Thanks again for this explanation.

I agree, someone has added a lot of manual rectangles, and manually calculated text. This would be a nightmare to maintain. It's embarrassing that I couldn't determine this on my own (my chart skills are moderate at best), but I couldn't imagine something so labor intensive being presented as a 'brilliant' solution - so, I was quite sure that I was missing something and wanted an expert opinion. thanks again, Jill
 
Jill.Pleau
While waiting Your rules about those manually added ... I checked that file.
I did some minor modification and
... all those pivots could show with one chart.
Here one sample.
 

Attachments

  • Capacity2.xlsx
    387.1 KB · Views: 4
vletm - this is great! It is exactly what I was hoping for!! As you saw in the previous charts. Management was hoping to find a way to add a 'Manhours" line (or outline surrounding the stacked column indicating a number of hours that workforce is available - as well as a 'capacity' line for each machine or group of machines that we chart. Do you have any thoughts about this? ty again! Jill
 
Jill.Pleau
As I wrote Mar 12 ... While waiting Your rules about those manually added ...
I had something, but You should able to give some data which management would hope to use as well as visual layout.
 
Jill.Pleau
As I wrote Mar 12 ... While waiting Your rules about those manually added ...
I had something, but You should able to give some data which management would hope to use as well as visual layout.
Hi vletm, I have attached an image of a chart that was created many years ago by a brilliant co worker. Unfortunately, the workbook was overly complicated, therefore the management team lost interest in it. However, this chart is missing is the stacked columns per month per part #. In summary;

1. Mgt would like to show stacked columns of hours per month per part #. (not depicted in the image)
2. Group multiple machines
3. Machine capacity - dependent on how many machines are in the group, and # shifts machine runs - (could define this in dataset)
3. Labor is a total of # of employees, and # of shifts employees work on each machine - (could define this in dataset)

Again, I appreciate your input! Jill
 

Attachments

  • Capacity_Groups_Labor_Capacity.JPG
    Capacity_Groups_Labor_Capacity.JPG
    93 KB · Views: 13
Jill.Pleau
That kind of chart is possible to do ... I tested to do a sample of it - Check test-sheet. It's not ready at all!
... but ...
#1 As I've tried to ask few times - data ... data ... data ... data = without a sample data.
That data should be smooth to add by user without extra duties.
Capacity2-file has data-sheet ... but is it useful with Your snapshot?

#2 You're using terms eg Group multiple machines - Have I read that term somewhere?
 

Attachments

  • Capacity2.xlsx
    395 KB · Views: 3
Last edited:
Hi vletm, I have attached data report with GROUP, CAPACITY, AND LABOR COLUMNS. Although this is for testing only, I believe all the data necessary is available. the Actual + Plan is not utilized any longer, so the data is not available. The Load with C/O (is carry over, it was originally developed to move work that could not POSSIBLY be completed, because there was more work then capacity or labor - to the next month.
 

Attachments

  • shopload.xls
    655 KB · Views: 4
Perhaps, I'm not explaining it properly, but the end goal hasn't changed at all. capacity and labor per machine , in theory could could change, but that number will come from the data.
 
Jill.Pleau
So far, there has been three kind of datas ... three kind of out puts.
Screenshot 2022-03-28 at 12.50.23.png
If CAPACITY/LABOR is always same, then this needs only one of those.
Is my the lastest sample file as Your the end goal?
 
I apologize, I did show you a different example of the data from many years ago.

Capacity is how many hours a machine COULD run IF we had enough employees to run it. Labor on the other hand is the amount of employee hours that are available to run the machine.

If capacity is 24 hours, but we only have 1 employee to run the machine then the labor would be 12 hours.

(The image that you are showing does not have the capacity at all. The Load w c/o uses the LABOR as the max possible capacity since we cannot run the machine more hours then we have employees to run it. )

Your sample is quite close, the only other thing that I would need is a line showing capacity of the group of machines. Each machine has the same capacity, but if I included 4 machines in my chart then the capacity would be 4 times. Thank you for persevering.
 

Attachments

  • 1648466505575.png
    1648466505575.png
    35.1 KB · Views: 2
Can we draw the bar of LABOR surrounding the stacked columns? I recall having a problem doing both in an older version of excel.
78232
 

Attachments

  • 1648468033300.png
    1648468033300.png
    31.7 KB · Views: 1
Jill.Pleau
(The image that you are showing does not have the capacity at all. The Load w c/o uses the LABOR as the max possible capacity since we cannot run the machine more hours then we have employees to run it. )
3rd time ... You the latest data shows that CAPACITY/LABOR is always same!
My last given file shows CAPACITY and LABOR.
Your sample is quite close, the only other thing that I would need is a line showing capacity of the group of machines. Each machine has the same capacity, but if I included 4 machines in my chart then the capacity would be 4 times.
The Colorful bar shows already sum of CAPACITY ....
as well as
LABOR can see as You've written.
Did You 'click that chart to see Data Table'?
Do You use same file and data as I have?
 
3rd time ... You the latest data shows that CAPACITY/LABOR is always same!
My last given file shows CAPACITY and LABOR.


The chart below has (4) machines on it, therefore the capacity would be 4 X 700 = 2800, the Labor would be 4 X 450 = 1800.
78235

The Colorful bar shows already sum of CAPACITY ....
as well as
LABOR can see as You've written.


In the image above, the colorful bar is the LOAD (the amount of work that we have scheduled to run),

What is NOT shown is the CAPACITY (the amount of hours the machines are able to run if we have enough employee hours to run them) is 2800 hours. The LABOR (amount of employee hours that we have available to run these machines) is 1800 hours.
 
You have wanted to see GROUP, CAPACITY, AND LABOR.
... I have no idea what is Your machine ... there is no that named column.

Your writings shows that CAPACITY/LABOR, which has same in every row of data (if not zero).

Seems You skipped to see Data Table.
... and seems that You're using Your own file - so I cannot comment Your writing.

LOAD ... maybe ... but which column?

Maybe Your shown chart won't show both CAPACITY and LABOR ... but mine shows those.
 
Machine = WC.
LOAD = TimeRem

Capacity/Labor is the same in every row because we need to summarize it when we group the machines.

I am using the data that is in the Capacity2.xlsb workbook, though I did add column M, N, and O to the DATA tab - it is not different.

In the Capacity2.xlsb that you provided, Chart tab is most like what we are looking for.
Chart is a stacked column of "TimeRem"
I would also like to show a "Capacity" line (goal line?)
I would also like to show a total Labor available by showing rectangles surrounding the stack columns

78237
 

Attachments

  • Capacity2.xlsb
    330.2 KB · Views: 4
a) Did You notice that there is newer Chart2 which uses Your the latest data Data2?
You should delete those Chart & Data -sheets.
b) Each month has own values as You could see from Data Table ... after You've done as I've asked few times
.... then do You really mean that ... which months values should show to as each months value .... hmm?
Screenshot 2022-03-28 at 21.11.05.png
 
vletm - Chart2 ONLY shows Capacity and Labor. there is no stacked columns with TimeRem . When I try to add the TimeRem I get an error message

78240
 
Good - now we have same chart ...

As I wrote #18 reply:
You have wanted to see GROUP, CAPACITY, AND LABOR. ... with Your the newest data.
I can add it there after I could know - which datas do You really need to see?
... as well as ...
Could You comment my b) -question?
 
b) Each month has own values as You could see from Data Table ... after You've done as I've asked few times
.... then do You really mean that ... which months values should show to as each months value .... hmm?


vletm - I'm sorry, I don't understand this comment / question
 
You've written like below with some settings
therefore the capacity would be 4 X 700 = 2800, the Labor would be 4 X 450 = 1800.

Data gives monthly sums of those three columns.
Screenshot 2022-03-28 at 22.52.56.png

'Your way' chart would show same value for each month
- - how possible? / with which logic?
I cannot understand Your blue sentence.
 
You are correct, in the last copy I sent, I had changed those hours to monthly hours.

Some of my charts will have 1 wc, others may have 2 or 3 or 4, the 700 hrs per month will be multiplied by the number of wcs included on the chart
 
Back
Top