i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for example..if
in column B i have items listed as book tire and car...i only want to get the
average of the books. i know i can get the total number of items and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

A simple way is to use Autofilter and subtotal function. Ill go through the
whole procedure in case you are not proficient in that area.
In your example, at the bottom of the amount column insert this formula:-
=SUBTOTAL(1,C2:C6)
Select all the columns of data and then Data-Filter-AutoFilter.
Click on the drop down arrow for the gear and select the required type and
you will have the average for the displayed rows of data.
I realise that this might meet all your needs, especially if you want to
display all the averages permanently but I hope it helps.
Look up the subtotal function in help for more information on it.

Regards,

OssieMac

"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for example..if
in column B i have items listed as book tire and car...i only want to get the
average of the books. i know i can get the total number of items and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

One way:

=SUMIF(C:C,"souvenirs",D:D)/COUNTIF(C:C,"souvenirs")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Eelinla" wrote in message
...
i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for
example..if
in column B i have items listed as book tire and car...i only want to get
the
average of the books. i know i can get the total number of items and the
sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for example..if
in column B i have items listed as book tire and car...i only want to get the
average of the books. i know i can get the total number of items and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

Let me try adding in a larger chart it might help to see what i need. none of
the formulas so far have worked..i keep getting some kind of error and as im
new to excel its really giving me a hard time. i am using office 2003. What i
need to do is take for example souveniers and get an average and place it in
in a cell of just the average of what the souveniers cost. getting the total
of amount and getting an average isn't the problem but getting the
conditional statement to work for just one type of gear is proving difficult
for me.


C D
E
Merchandise Gear Amount

20 Adult Cheesehead Souveniers $1,075.20
21 Ear Muffs Gear $2,197.73
22 Large Gym Shorts Apparel $202.30
23 Lg Ski Vest Gear $299.16
24 XXL Gym Shorts Apparel $23.31
25 Medium Gym Shorts Souveniers $37.86
27 Plastic Beer Mug Souveniers $797.12
28 Shot Glass Souveniers $323.20
29 Sm Ski Vest Gear $1,041.66
30 Small Gym Shorts Apparel $243.60
31 Team Picture Souveniers $496.62
32 XL Gym Shorts Apparel $512.80
33 Glass Beer Mug Souveniers $135.63
34 XL Leather Jacket Gear $776.58

average of souveniers cell here @ E36

i need to come up with a way to get $477.60 to show up in cell E36 for
example, which is the average of souveniers. I also need to leave the list
intact.

"Teethless mama" wrote:

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for example..if
in column B i have items listed as book tire and car...i only want to get the
average of the books. i know i can get the total number of items and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))

To execute an array formula you have to press ctrl+shift+enter, not just
enter. If you do it right it will put brackets { } around the formula. Don't
manually put it in yourself.


"Eelinla" wrote:

Let me try adding in a larger chart it might help to see what i need. none of
the formulas so far have worked..i keep getting some kind of error and as im
new to excel its really giving me a hard time. i am using office 2003. What i
need to do is take for example souveniers and get an average and place it in
in a cell of just the average of what the souveniers cost. getting the total
of amount and getting an average isn't the problem but getting the
conditional statement to work for just one type of gear is proving difficult
for me.


C D
E
Merchandise Gear Amount

20 Adult Cheesehead Souveniers $1,075.20
21 Ear Muffs Gear $2,197.73
22 Large Gym Shorts Apparel $202.30
23 Lg Ski Vest Gear $299.16
24 XXL Gym Shorts Apparel $23.31
25 Medium Gym Shorts Souveniers $37.86
27 Plastic Beer Mug Souveniers $797.12
28 Shot Glass Souveniers $323.20
29 Sm Ski Vest Gear $1,041.66
30 Small Gym Shorts Apparel $243.60
31 Team Picture Souveniers $496.62
32 XL Gym Shorts Apparel $512.80
33 Glass Beer Mug Souveniers $135.63
34 XL Leather Jacket Gear $776.58

average of souveniers cell here @ E36

i need to come up with a way to get $477.60 to show up in cell E36 for
example, which is the average of souveniers. I also need to leave the list
intact.

"Teethless mama" wrote:

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for example..if
in column B i have items listed as book tire and car...i only want to get the
average of the books. i know i can get the total number of items and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

Yup that is what I am doing. I get a #DIV/0 error. I have no clue why or how
to fix it..All I do know is that I am frustrated. :(

"Teethless mama" wrote:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))

To execute an array formula you have to press ctrl+shift+enter, not just
enter. If you do it right it will put brackets { } around the formula. Don't
manually put it in yourself.


"Eelinla" wrote:

Let me try adding in a larger chart it might help to see what i need. none of
the formulas so far have worked..i keep getting some kind of error and as im
new to excel its really giving me a hard time. i am using office 2003. What i
need to do is take for example souveniers and get an average and place it in
in a cell of just the average of what the souveniers cost. getting the total
of amount and getting an average isn't the problem but getting the
conditional statement to work for just one type of gear is proving difficult
for me.


C D
E
Merchandise Gear Amount

20 Adult Cheesehead Souveniers $1,075.20
21 Ear Muffs Gear $2,197.73
22 Large Gym Shorts Apparel $202.30
23 Lg Ski Vest Gear $299.16
24 XXL Gym Shorts Apparel $23.31
25 Medium Gym Shorts Souveniers $37.86
27 Plastic Beer Mug Souveniers $797.12
28 Shot Glass Souveniers $323.20
29 Sm Ski Vest Gear $1,041.66
30 Small Gym Shorts Apparel $243.60
31 Team Picture Souveniers $496.62
32 XL Gym Shorts Apparel $512.80
33 Glass Beer Mug Souveniers $135.63
34 XL Leather Jacket Gear $776.58

average of souveniers cell here @ E36

i need to come up with a way to get $477.60 to show up in cell E36 for
example, which is the average of souveniers. I also need to leave the list
intact.

"Teethless mama" wrote:

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky part. I only
need to get the average for some of the items, based on type. for example..if
in column B i have items listed as book tire and car...i only want to get the
average of the books. i know i can get the total number of items and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how would i
go about getting the average prices for each if there were multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

If you're getting a #DIV/0! error that means "Souvenirs" in your formula
does not match the "Souveniers" in your table.

There is a spelling difference in the formula compared to the spelling in
your table:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))
20 Adult Cheesehead Souveniers $1,075.20

Biff

"Eelinla" wrote in message
...
Yup that is what I am doing. I get a #DIV/0 error. I have no clue why or
how
to fix it..All I do know is that I am frustrated. :(

"Teethless mama" wrote:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))

To execute an array formula you have to press ctrl+shift+enter, not just
enter. If you do it right it will put brackets { } around the formula.
Don't
manually put it in yourself.


"Eelinla" wrote:

Let me try adding in a larger chart it might help to see what i need.
none of
the formulas so far have worked..i keep getting some kind of error and
as im
new to excel its really giving me a hard time. i am using office 2003.
What i
need to do is take for example souveniers and get an average and place
it in
in a cell of just the average of what the souveniers cost. getting the
total
of amount and getting an average isn't the problem but getting the
conditional statement to work for just one type of gear is proving
difficult
for me.


C D
E
Merchandise Gear
Amount

20 Adult Cheesehead Souveniers
$1,075.20
21 Ear Muffs Gear $2,197.73
22 Large Gym Shorts Apparel $202.30
23 Lg Ski Vest Gear $299.16
24 XXL Gym Shorts Apparel $23.31
25 Medium Gym Shorts Souveniers $37.86
27 Plastic Beer Mug Souveniers $797.12
28 Shot Glass Souveniers $323.20
29 Sm Ski Vest Gear $1,041.66
30 Small Gym Shorts Apparel $243.60
31 Team Picture Souveniers $496.62
32 XL Gym Shorts Apparel $512.80
33 Glass Beer Mug Souveniers $135.63
34 XL Leather Jacket Gear $776.58

average of souveniers cell here @ E36

i need to come up with a way to get $477.60 to show up in cell E36 for
example, which is the average of souveniers. I also need to leave the
list
intact.

"Teethless mama" wrote:

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky
part. I only
need to get the average for some of the items, based on type. for
example..if
in column B i have items listed as book tire and car...i only want
to get the
average of the books. i know i can get the total number of items
and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how
would i
go about getting the average prices for each if there were
multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

Well that helped a ton. I still couldn't get teethless mama's formula to work
though. It gave me a value error. I did however get RagDeyR's to work. It
was similar to what i had originally tryed, but a little different in format.
I was making a spelling mistake on the word "souveniers" and even though I
did double check it, I didn't see it. Guess walking away for a few minutes
would have done me wonders. Thanks for all the input, much appreciated.

"T. Valko" wrote:

If you're getting a #DIV/0! error that means "Souvenirs" in your formula
does not match the "Souveniers" in your table.

There is a spelling difference in the formula compared to the spelling in
your table:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))
20 Adult Cheesehead Souveniers $1,075.20

Biff

"Eelinla" wrote in message
...
Yup that is what I am doing. I get a #DIV/0 error. I have no clue why or
how
to fix it..All I do know is that I am frustrated. :(

"Teethless mama" wrote:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))

To execute an array formula you have to press ctrl+shift+enter, not just
enter. If you do it right it will put brackets { } around the formula.
Don't
manually put it in yourself.


"Eelinla" wrote:

Let me try adding in a larger chart it might help to see what i need.
none of
the formulas so far have worked..i keep getting some kind of error and
as im
new to excel its really giving me a hard time. i am using office 2003.
What i
need to do is take for example souveniers and get an average and place
it in
in a cell of just the average of what the souveniers cost. getting the
total
of amount and getting an average isn't the problem but getting the
conditional statement to work for just one type of gear is proving
difficult
for me.


C D
E
Merchandise Gear
Amount

20 Adult Cheesehead Souveniers
$1,075.20
21 Ear Muffs Gear $2,197.73
22 Large Gym Shorts Apparel $202.30
23 Lg Ski Vest Gear $299.16
24 XXL Gym Shorts Apparel $23.31
25 Medium Gym Shorts Souveniers $37.86
27 Plastic Beer Mug Souveniers $797.12
28 Shot Glass Souveniers $323.20
29 Sm Ski Vest Gear $1,041.66
30 Small Gym Shorts Apparel $243.60
31 Team Picture Souveniers $496.62
32 XL Gym Shorts Apparel $512.80
33 Glass Beer Mug Souveniers $135.63
34 XL Leather Jacket Gear $776.58

average of souveniers cell here @ E36

i need to come up with a way to get $477.60 to show up in cell E36 for
example, which is the average of souveniers. I also need to leave the
list
intact.

"Teethless mama" wrote:

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky
part. I only
need to get the average for some of the items, based on type. for
example..if
in column B i have items listed as book tire and car...i only want
to get the
average of the books. i know i can get the total number of items
and the sum
of the specific items, but i can't seem to get the average of them.



so for example the following in colums B C and D respectively how
would i
go about getting the average prices for each if there were
multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99

I can understand your spelling problem, especially in the way you spelled my
name.<bg


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Eelinla" wrote in message
...
Well that helped a ton. I still couldn't get teethless mama's formula to
work
though. It gave me a value error. I did however get RagDeyR's to work. It
was similar to what i had originally tryed, but a little different in
format.
I was making a spelling mistake on the word "souveniers" and even though I
did double check it, I didn't see it. Guess walking away for a few
minutes
would have done me wonders. Thanks for all the input, much appreciated.

"T. Valko" wrote:

If you're getting a #DIV/0! error that means "Souvenirs" in your formula
does not match the "Souveniers" in your table.

There is a spelling difference in the formula compared to the spelling
in
your table:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))
20 Adult Cheesehead Souveniers $1,075.20

Biff

"Eelinla" wrote in message
...
Yup that is what I am doing. I get a #DIV/0 error. I have no clue why
or
how
to fix it..All I do know is that I am frustrated. :(

"Teethless mama" wrote:

=AVERAGE(IF(D20:D34="Souvenirs",E20:E34))

To execute an array formula you have to press ctrl+shift+enter, not
just
enter. If you do it right it will put brackets { } around the
formula.
Don't
manually put it in yourself.


"Eelinla" wrote:

Let me try adding in a larger chart it might help to see what i
need.
none of
the formulas so far have worked..i keep getting some kind of error
and
as im
new to excel its really giving me a hard time. i am using office
2003.
What i
need to do is take for example souveniers and get an average and
place
it in
in a cell of just the average of what the souveniers cost. getting
the
total
of amount and getting an average isn't the problem but getting the
conditional statement to work for just one type of gear is proving
difficult
for me.


C D
E
Merchandise Gear
Amount

20 Adult Cheesehead Souveniers
$1,075.20
21 Ear Muffs Gear $2,197.73
22 Large Gym Shorts Apparel $202.30
23 Lg Ski Vest Gear $299.16
24 XXL Gym Shorts Apparel $23.31
25 Medium Gym Shorts Souveniers $37.86
27 Plastic Beer Mug Souveniers
$797.12
28 Shot Glass Souveniers $323.20
29 Sm Ski Vest Gear $1,041.66
30 Small Gym Shorts Apparel $243.60
31 Team Picture Souveniers $496.62
32 XL Gym Shorts Apparel $512.80
33 Glass Beer Mug Souveniers $135.63
34 XL Leather Jacket Gear $776.58

average of souveniers cell here @ E36

i need to come up with a way to get $477.60 to show up in cell E36
for
example, which is the average of souveniers. I also need to leave
the
list
intact.

"Teethless mama" wrote:

=AVERAGE(IF(C2:C100="Souvenirs",D2:D100))
ctrl+shift+enter, not just enter

For XL2007
=AVERAGEIF(C2:C100,"Souvenirs",D2:D100)
just enter


"Eelinla" wrote:

i need to figure the average of some items. Here is the tricky
part. I only
need to get the average for some of the items, based on type.
for
example..if
in column B i have items listed as book tire and car...i only
want
to get the
average of the books. i know i can get the total number of
items
and the sum
of the specific items, but i can't seem to get the average of
them.



so for example the following in colums B C and D respectively
how
would i
go about getting the average prices for each if there were
multiples of each
type of gear.

merchandise gear amount
ear muffs gear $290.98
team picture souvenirs $56.98
xl gym shorts Apparel $35.98
Glass Beer Mug Souvenirs $24.95
M t-shirt Apparel $19.99