im sure there is a better way, but this is how i would do it...

assuming column a has your $ amounts and column b is your % amounts starting
in row 2...

in columns c through n, enter the following formulas respectively and paste
down for each row:
=SUM(D2:N2)
=AND(A299,A2<=199)*2
=AND(A2199,A2<=299)*3
=AND(A2299,A2<=399)*4
=AND(A2399,A2<=499)*5
=AND(A2499,A2<=599)*6
=AND(A2599,A2<=699)*7
=AND(A2699,A2<=799)*8
=AND(A2799,A2<=899)*9
=AND(A2899,A2<=999)*10
=(A2999)*11


when all is said and done column c will make your categories based on the
total it provides. sort the spreadsheet by this column. then use the
subtotal option in the data menu. you would want to use column c for "at
each change in", average for "use function", and column b for "add subtotal
to"




"Hoov" wrote:

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