I am not sure how to write the "= but <" . I'm not proficient in Excel, so
I'm not real familiar with your explanation. An actual formula example would
be the most helpful.

"joemeshuggah" wrote:

perhaps add a column and use an if statement to categorize column a, sort by
this new column, and then use datasubtotal to average column a at each
change in category

"Hoov" wrote:

Hi, I am using the newest version of Excel on Windows 7, and I need some help
with a complex calculation.

In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells
B1-B100, I have percentages ranging from 0%-100%. I want to break out the
cells in A1-A100 in several groups, like follows: $1-$99, $100-$199,
$200-$299, and so on up to $1000. Then, I need to calculate the average
percentage for jobs in those categories. So, for the category of $1-$99,
lets say there are two cells with amounts in that range, A1 and A2. Their
percentages in B1 and B2 are 40% and 60%, respectively. Well, the average
would then be 50%. I don't need any help with something that simple, but
when there are hundreds of large numbers involved, I need Excel to
automatically calculate this.

My thoughts on what the equation would look like in layman's terms would be:

AVG ((IF A1=1 but <100, B1) + (IF A2=1 but <100, B2)

With the above example, and this equation, I would be left with the average
of B1 (or 40%) + B2 (or 60%), and the answer would be 50%. I really hope
that this makes sense, because I am in desperate need of figuring this out.
I have no idea how to properly enter such an equation into Excel.

Thanks so much,

Aaron