Calculating Averages - Very Complex PLEASE HELP
 

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

Calculating Averages - Very Complex PLEASE HELP
 

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

Calculating Averages - Very Complex PLEASE HELP
 

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

Calculating Averages - Very Complex PLEASE HELP
 

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

Calculating Averages - Very Complex PLEASE HELP
 

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

Calculating Averages - Very Complex PLEASE HELP
 

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

=SUM(D2:N2)
=(A2<=99)*1
=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

"joemeshuggah" wrote:

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

Calculating Averages - Very Complex PLEASE HELP
 

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

"T. Valko" wrote:

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


.

Calculating Averages - Very Complex PLEASE HELP
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Hoov" wrote in message
...
The array formula worked beautifully! No flaws whatsoever! Thank you so
much!

"T. Valko" wrote:

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


.