statistical variance in movies incomes

[Obsidian]

Hello to everyone, i'm new to this board.
I hope this is the right place to ask this question.
I have a excel worksheet with a list of every movie that came out in 2008.
The list is ordered by income of every movie, for example:


Zorro 290,000
Benjamin Button 400,000

And so on.
I have to divide the movies in four groups, and every group has to to have maximum omogeneity in the incomes. That is to say, the average variance of the incomes of every group has to be minimum.

has anybody have an idea of how to do this?

I am sorry for the mistakes, i am not a native english speaker

[mewingkitty]

Could you post your workbook?

If I understand correctly. You want to split it into a rating type system based on 4 categories, top 25%, 50-75%, 25-50%, 0-25%, of the income for each movie. But instead of a top 25%, you want the top movies which far surpass the others, then two groups of the middle income, and a group for those far lower?

I think?

Let me know if that makes sense.

[Obsidian]

Could you post your workbook?

If I understand correctly. You want to split it into a rating type system based on 4 categories, top 25%, 50-75%, 25-50%, 0-25%, of the income for each movie. But instead of a top 25%, you want the top movies which far surpass the others, then two groups of the middle income, and a group for those far lower?

I think?

Let me know if that makes sense.

Uhm, i'm not sure i've explained myself.
I am studying analysis of variance with a friend of mine who is a statistics student, so it's more of a statistics question than an excel one i have to admit.

So, yes, as you said, i need to divide the movies in four groups. But in which manner?

I have to divide them so that the average of the variances of he income of every group is lowest.

For example, if i sort them out by top 25%, 50-75% and so on, i could obtain in the first group a movie whose income is very lower than the other ones, but that nonetheless fits that group, and so that movie would make the statystical variance of the group very high, and instead i don't want that.

So in this case i'd need to put that movie in the lower group, and so on. But i need a function to do this automatically.

I was reading the analysis of variance section on wikipedia and it has the mathematical solution to my problem, but it's too thick for me to understand and to apply to excel.

Please do let me know if i have explained myself

And also if this is not the place to ask, where could i try ^^

[etaf]

if you add the Analysis ToolPak inj excel
http://www.microbiologybytes.com/maths/toolpak.html

you have a function
ANalysis Of VAriance: ANOVA
http://www.microbiologybytes.com/maths/1011-20.html

[Obsidian]

if you add the Analysis ToolPak inj excel
http://www.microbiologybytes.com/maths/toolpak.html

you have a function
ANalysis Of VAriance: ANOVA
http://www.microbiologybytes.com/maths/1011-20.html

That was excellent advice.

So i used the "anova: single factor" function.

But the problem is that this function lets me measure the average variance inside the group, and between the groups, but before i have to arbitrarily divide the groups by my own means.

Insted i need something that automatically creates the groups by the principle of lowest average variance for every group.

Can you help me?