software

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!

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!

In article gt;,
quot;Paulquot; gt; wrote:

gt; Dunno if this'll make sence but I'll try to explain my problem.
gt;
gt; I want to find the mean and mode of a certain range. But I need the range
gt; for each item in list and I'll be adding and taking away from that list. Like
gt; this:
gt;
gt; item1 5
gt; item1 8
gt; item1 2
gt; item1 5
gt; item2 9
gt; item2 10
gt; item2 3
gt; item2 4
gt;
gt; Say thats my worksheet....I want the mean of all the ones for item1, item2
gt; ect.... Is there a way to lookup which the numbers for each item and give the
gt; mean/mode without manually sececting them. there are thousends in for each
gt; item and I'll be adding more so I need my mean/mode to update.
gt;
gt; If anyway can make sense all that and give me some help I'll be well
gt; happy!!!Thanks!

=AVERAGE(IF(A1:A8=quot;item1quot;,B1:B8))
and
=MODE(IF(A1:A8=quot;item1quot;,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.

Paul wrote:
gt;
gt; Dunno if this'll make sence but I'll try to explain my problem.
gt;
gt; I want to find the mean and mode of a certain range. But I need the range
gt; for each item in list and I'll be adding and taking away from that list. Like
gt; this:
gt;
gt; item1 5
gt; item1 8
gt; item1 2
gt; item1 5
gt; item2 9
gt; item2 10
gt; item2 3
gt; item2 4
gt;
gt; Say thats my worksheet....I want the mean of all the ones for item1, item2
gt; ect.... Is there a way to lookup which the numbers for each item and give the
gt; mean/mode without manually sececting them. there are thousends in for each
gt; item and I'll be adding more so I need my mean/mode to update.
gt;
gt; If anyway can make sense all that and give me some help I'll be well
gt; happy!!!Thanks!

--

Dave Peterson

=MEAN(IF(A1:A100=quot;item1quot;,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)

quot;Paulquot; gt; wrote in message
...
gt; Dunno if this'll make sence but I'll try to explain my problem.
gt;
gt; I want to find the mean and mode of a certain range. But I need the range
gt; for each item in list and I'll be adding and taking away from that list.
Like
gt; this:
gt;
gt; item1 5
gt; item1 8
gt; item1 2
gt; item1 5
gt; item2 9
gt; item2 10
gt; item2 3
gt; item2 4
gt;
gt; Say thats my worksheet....I want the mean of all the ones for item1, item2
gt; ect.... Is there a way to lookup which the numbers for each item and give
the
gt; mean/mode without manually sececting them. there are thousends in for each
gt; item and I'll be adding more so I need my mean/mode to update.
gt;
gt; If anyway can make sense all that and give me some help I'll be well
gt; happy!!!Thanks!
I put your data in Range A1:B8

then in Cell F1 entered quot;item1quot;
in Cell F2 entered quot;item2quot;

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

HTHquot;Paulquot; wrote:

gt; Dunno if this'll make sence but I'll try to explain my problem.
gt;
gt; I want to find the mean and mode of a certain range. But I need the range
gt; for each item in list and I'll be adding and taking away from that list. Like
gt; this:
gt;
gt; item1 5
gt; item1 8
gt; item1 2
gt; item1 5
gt; item2 9
gt; item2 10
gt; item2 3
gt; item2 4
gt;
gt; Say thats my worksheet....I want the mean of all the ones for item1, item2
gt; ect.... Is there a way to lookup which the numbers for each item and give the
gt; mean/mode without manually sececting them. there are thousends in for each
gt; item and I'll be adding more so I need my mean/mode to update.
gt;
gt; If anyway can make sense all that and give me some help I'll be well
gt; happy!!!Thanks!