Trouble with statistical functions and excel

Copper Contributor

Hey there,

 

I'm having serious trouble with a problem and am hoping for some help. 

I know it's alot, I'd be greatful for even only parts of an solution. 

 

I've got an excel file with the hours through April and the number of starts and landings for each (attached below).

 

I'm suppost to create a distribution function, which gives an appropriate probability of one of the values of the record. 

Then I should develop the distribution from obove by adding a normally distributed error term with a standard deviation of 12 aircrafts to be able to draw as well meaningful (!) values apart from the record. Show the airport cases of three simulated months (October, November, December) in hours in a histogram.

Next: The airport knows that between 4% and 9% of all aircrafts can’t start because of an engine failure, so they have to stay in a predetermined area. Assume the share of Lufthansa of all aircrafts is beta-distributed with 𝛼=2 and 𝛽=5. Simulate the number of Lufthansa aircrafts, which can’t start for 500 hours, and visualize your results in a boxplot diagram.

And last: Each airline has an own predetermined area for their aircrafts that can’t start because of an engine failure. The area for the Lufthansa is limited to three aircrafts. Report the share of hours, when this limit is no sufficient for all aircrafts, which can’t start. How many aircrafts should have space in this area, if you want to cover the demand at 95% of all hours?

 

I know that I won't learn much by doing it this way, but I'm very desperate and need to finish this soon. I might have to present this so I will attempt to understand whatever solution you offer.

Again there is no need for anyone to help me with every single thing I've asked for here, every little help and answer will be greatly appreciated.

8 Replies

@Mmrtlm  ....  I can't say that I fully understand all parts of the assignment, off-hand.

 

But for the simulations, consider using NORMINV(RAND(),...) and BETAINV(RAND(),...) .

 

If that is "obvious" to you, please explain what aspect of the assignment is giving you trouble.

@Joe User This is actually everything I have concerning the assignment.

 

I'm having trouble with which function to use and when to simulate more than once and only once, when to use INV and when VERT.

 

E.g. for the first part: Create a distribution function, which gives you an appropriate probability of one of the values of the record

Do I AVERAGE and STDEV.P the values, and give a RAND() for the x?

So: =NORM.INV(RAND(),AVERAGE("values"),STDEV.P("values"))

I have a feeling that doesn't do what is asked for.

@Mmrtlm  ....  In general, we can help with how to use Excel to solve problems that are well-defined. But usually, we rely on you to "well-define" the problem.

 

Sometimes, we can offer insight that might clarify the problem. But the most reliable source of such clarification is the assignment, the instructor and other students who are familiar with the coursework to-date.

 

I'm afraid that I will not be of much help to you.  And worse, I'm afraid that I might misdirect you.

 

I find inconsistencies that confuse me.  For example....

 

-----

You wrote: ``for the first part: Create a distribution function, which gives you an appropriate probability of one of the values of the record [....] So: =NORM.INV(RAND(),AVERAGE("values"),STDEV.P("values"))``

 

Well, NORM.DIST("oneValue", AVERAGE("values"), STDEV.P("values"), 0) perhaps -- although I would use the difference of two cumulative NORM.DIST calls.  (Note: "VERT" = "DIST" in English.)

 

That requires more explanation.  But the point is:  your data is uniformally distributed, not normally distributed(!).

 

And that's just the tip of the iceberg.

 

I want to help, but I'm afraid that I cannot.  Good luck!

 

@Joe User Misdirecting me shouldn't worry you, it's my risk by asking here, but anyways I wouldn't adapt any solution without thinking it through and understanding myself.

 

You say that my data is uniformly distributed. How can one tell? And is there a function for uniformly distribution?

 

 If you're unsure or don't want to answer any statistical questions, I completely understand and next time I'll make sure to clearify all non excel concerning parts first. 

@Mmrtlm  ....  Yes, I'm unsure of my answers to your questions.  So I think it is best that I step back.

 

You wrote:  ``You say that my data is uniformly distributed. How can one tell?``

 

Graphically.

 

airport data distrib.jpg

 

The blue histogram is the actual distribution of the data.  The orange curve is the expected normal distribution of the data.  See the attached Excel file.

 

@Mmrtlm  ....  Please provide the __original__ and complete text of the assignment -- not your interpretation (and translation?).  Ideally, provide a PDF.  Alternatively, provide an image.

 

Also, please provide any __original__ data file that accompanies the assignment, if that is different from the Excel attachment.

 

Note:  This is for my own edification.  I would appreciate the original information, even if you have progressed beyond your needs expressed here.

 

I am still trying to understand the assignment.  I struggle with some details in the description and in the data.

 

Thanks.

@Joe User You pretty much already have everything I've got concerning information. 

 

I can't provide you with the pdf, but the following is copied directly from the assignment without any edits from my side and the excel file from my original post is also unedited:

 

An airport wants to have an analysis of its processes. They gave you the number of starts and landings for every hour of April 2021.
a) Create a distribution function, which gives you an appropriate probability of one of the values of the record.
b) Develop your distribution from a) by adding a normally distributed error term with a standard deviation of 12 aircrafts to be able to draw as well meaningful (!) values apart from the record. Show the airport cases of three simulated months (October, November, December) in hours in a histogram.
c) The airport knows that between 4% and 9% of all aircrafts can’t start because of an engine failure, so they have to stay in a predetermined area. Assume the share of Lufthansa of all aircrafts is beta-distributed with 𝛼=2 and 𝛽=5. Simulate the number of Lufthansa aircrafts, which can’t start for 500 hours, and visualize your results in a boxplot diagram.
d) Each airline has an own predetermined area for their aircrafts that can’t start because of an engine failure. The area for the Lufthansa is limited to three aircrafts. Report the share of hours, when this limit is no sufficient for all aircrafts, which can’t start. How many aircrafts should have space in this area, if you want to cover the demand at 95% of all hours?

 

We (mainly someone from my group) solved the entire assignment and I understand it now, so *cough *cough if you need any information on how we solved it feel free to ask. 

 

Also if your wondering, my professors aren't native english speakers, thus some mistakes/vague tasks.

@Mmrtlm  wrote: ``my professors aren't native english speakers, thus some mistakes/vague tasks.``

 

That is what I thought.  Thanks for the confirmation.

 

You wrote: ``We (mainly someone from my group) solved the entire assignment and I understand it now, so *cough *cough if you need any information on how we solved it feel free to ask. ``

 

I would appreciate it if you posted an Excel file with your solutions.  It might help me understand how to interpret the assignment.

 

(If you prefer, you can send the Excel file to me directly:  joeu20045 "at" gmail.com.)

 

Thanks.