Lookup in a range.

[Guest]

Dunno if this'll make sence but I'll try to explain my problem.

I want to find the mean and mode of a certain range. But I need the range
for each item in list and I'll be adding and taking away from that list. Like
this:

item1 5
item1 8
item1 2
item1 5
item2 9
item2 10
item2 3
item2 4

Say thats my worksheet....I want the mean of all the ones for item1, item2
ect.... Is there a way to lookup which the numbers for each item and give the
mean/mode without manually sececting them. there are thousends in for each
item and I'll be adding more so I need my mean/mode to update.

If anyway can make sense all that and give me some help I'll be well
happy!!!Thanks!

 

[Domenic]

Assuming that A1:B8 contains your data, let D12 contain Item1 and
Item2, then try the following...

E1, copied down:

=AVERAGE(IF(A$1:A$8=D1,B$1:B$8))

....confirmed with CONTROL+SHIFT+ENTER

F1, copied down:

=MODE(IF(A$1:A$8=D1,B$1:B$8))

....confirmed with CONTROL+SHIFT+ENTER

Note that you can get a unique list of items for Column A by using
'Advanced Filter', checking 'Unique records only', and choosing the
location to copy to.

Hope this helps!

 

[Dave Peterson]

=AVERAGE(IF(A1:A8="item1",B1:B8))
and
=MODE(IF(A1:A8="item1",B1:B8))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[Bob Phillips]

=MEAN(IF(A1:A100="item1",B1:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

I put your data in Range A1:B8

then in Cell F1 entered "item1"
in Cell F2 entered "item2"

In cell G1 entered =SUMIF($A$1:$A$8,F1,$B$1:$B$8)/COUNTIF($A$1:$A$8,F1)
andcopied down to G2.

HTH