Matching a Weibull Distribution to a Data Set in Excel

Uploaded by PSUwind on 07.08.2012

Now I'm going to show how to match a Weibull distribution
to your wind data. This is just one method of doing it,
they are a lot of different approaches. We're looking at
the histogram that was created before, which
we're going to try overlay a Weibull distribution to this and match it.
So if we go back to the original data, we need to have some idea of what the wind power density was
of this site. So we're going
calculate that for each time step,
1/2 times rho, times velocity in m/s cubed.
We're going to do that for every time step, and copy it down.
Take the average of those values,
you see it's 115.3 W/m squared. So that's power per unit area
of the swept rotor. Now if we go back to our data
in our plot, we're going to create now a column
with the Weibull distribution. I'm going to use the built in Weibull function in Excel
It's asking for the velocity, which is going to be our bin
value. And then alpha is the shape factor,
which I'll talk about in a second.
And then the beta is the lambda, which is our
scale factor. Then it's asking if this is a cumulative function or not,
and we don't want the cumulative function, so we're going to say false. And now I'm going to
fix those shape and scale factors
so that they don't change as I copy this down.
Raising the dollar signs. So I copy
this down and this creates the Weibull curve I've already gutted automatically.
Automatically plotting over here, so you can see it overlaid.
I wanted to point out that the average velocity
of the this data set had to be 4 and 1/2 m/s, and in order to calculate
the lambda, which is a function of the average velocity is also a function of the
scale factors. And we don't necessarily now that, so we're guessing 2,
which is the Rayleigh distribution as a starting point. But that's really
the number that we need match here. So in order to match it I'm going to again calculate the
the wind power density
1/2 times rho
times again the bin velocity cubed
and then we also need multiply this by
the Weibull distribution or the percentage of time which it spends
at each of the bins. And then
we're going to sum this column, instead of average it because it's already a weighted
average. And we get 106.59 W/m squared. And just for comparison purposes
I'm going to bring the value from the other page over, it's
115.3 W/m squared.
So you can just adjust k here,
and you can fiddle with it until they match. That's one way of doing it, or you can use the solver function.
And with the solver function we're going to set the objective cell,
to be this number, which we want to match this number.
The value that we're going to match is this 115.3, and we're going to do
it by changing the k value. So it will run through all the iterations.
It's found a solution, and lets say we want keep that, and it comes out to
1.85 for the shape factor. You can see that the plots
don't perfectly overlay one another, that's because the original data may not have been the
perfect Weibull distribution, but you do have them matched to have similar
power density which is important in terms of wind resource assessment.