average

[Guest]

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

 

[Guest]

A simple way is to use Autofilter and subtotal function. I’ll 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

 

[RagDyeR]

One way:

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

--

HTH,

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

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

 

[Guest]

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

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

 

[Guest]

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.

 

[Guest]

=AVERAGE(IF(D2034="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.

 

[Guest]

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.

 

[T. Valko]

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(D2034="Souvenirs",E20:E34))
20 Adult Cheesehead Souveniers $1,075.20

Biff

 

[Guest]

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.

 

[Ragdyer]

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