Try this array formula** :

=AVERAGE(IF(A1:A100=1,IF(A1:A100<=99,B1:B100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Or, use some cells to hold the boundaries:

D1 = 1...E1 = 99
D2 = 100...E2 = 199
D3 = 200...E3 = 299
etc
etc

=AVERAGE(IF(A$1:A$100=D1,IF(A$1:A$100<=E1,B$1:B$1 00)))

Copy down as needed

--
Biff
Microsoft Excel MVP


"Hoov" wrote in message
...
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