Calculating Averages - Very Complex PLEASE HELP

[Hoov]

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

 

[joemeshuggah]

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

 

[T. Valko]

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$100)))

Copy down as needed

 

[Hoov]

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]

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(A2>99,A2<=199)*2
=AND(A2>199,A2<=299)*3
=AND(A2>299,A2<=399)*4
=AND(A2>399,A2<=499)*5
=AND(A2>499,A2<=599)*6
=AND(A2>599,A2<=699)*7
=AND(A2>699,A2<=799)*8
=AND(A2>799,A2<=899)*9
=AND(A2>899,A2<=999)*10
=(A2>999)*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"

 

[joemeshuggah]

sorry...use these in columns c through n...i was missing one in the previous
string

=SUM(D2:N2)
=(A2<=99)*1
=AND(A2>99,A2<=199)*2
=AND(A2>199,A2<=299)*3
=AND(A2>299,A2<=399)*4
=AND(A2>399,A2<=499)*5
=AND(A2>499,A2<=599)*6
=AND(A2>599,A2<=699)*7
=AND(A2>699,A2<=799)*8
=AND(A2>799,A2<=899)*9
=AND(A2>899,A2<=999)*10
=(A2>999)*11

 

[Hoov]

The array formula worked beautifully! No flaws whatsoever! Thank you so much!

 

[T. Valko]

You're welcome. Thanks for the feedback!