Solve:can this be done?

[stuffandthings]

Here's the basic set up:

A| B
1| 456
1| 52
1| 78
1| 91
1| 12
1| 6
1| 7
2| 12
2| 80
2| 9
2| 15
2| 12
2| 6


And here's the question:
Basically, column A represents an item # and column B represent the item price.

What I want to do is - and please know I'm sincere when I say I've really been trying to solve this myself - every time A:A = 1, I want to average the lowest 3 prices in B:B relating to A:A1... and then average the lowest 3 prices in B:B relating to the A:A = 2.

Maybe I've completely over-thought this one.

Keep in mind, too... that this is a really basic example of what I'm trying to do. There are over 1400 individual items, each being sold at multiple prices. I understand enough of excel that a similarly basic example will suffice - one that I can just create 1400+ times over.

Thanks to anyone ahead of time for your help.

Stuffandthings

[Bob Umlas]

ctrl/shift/enter this:
=AVERAGE(SMALL(IF(A1:A2000=1,B1:B2000,""),ROW(1:3)))
and/or
=AVERAGE(SMALL(IF(A1:A2000=2,B1:B2000,""),ROW(1:3)))

"stuffandthings"
<[email protected]> wrote in
message news:[email protected]...
>
> Here's the basic set up:
>
> A| B
> 1| 456
> 1| 52
> 1| 78
> 1| 91
> 1| 12
> 1| 6
> 1| 7
> 2| 12
> 2| 80
> 2| 9
> 2| 15
> 2| 12
> 2| 6
>
>
> And here's the question:
> Basically, column A represents an item # and column B represent the
> item price.
>
> What I want to do is - and please know I'm sincere when I say I've
> really been trying to solve this myself - every time A:A = 1, I want to
> average the lowest 3 prices in B:B relating to A:A1... and then average
> the lowest 3 prices in B:B relating to the A:A = 2.
>
> Maybe I've completely over-thought this one.
>
> Keep in mind, too... that this is a really basic example of what I'm
> trying to do. There are over 1400 individual items, each being sold at
> multiple prices. I understand enough of excel that a similarly basic
> example will suffice - one that I can just create 1400+ times over.
>
> Thanks to anyone ahead of time for your help.
>
> Stuffandthings
>
>
> --
> stuffandthings
> ------------------------------------------------------------------------
> stuffandthings's Profile:
http://www.excelforum.com/member.php...o&userid=31579
> View this thread: http://www.excelforum.com/showthread...hreadid=512714
>

[Domenic]

Assuming that A1:B13 contains your data...

Let D1 contain 1

Let D2 contain 2

Enter the following formula in E1 and copy down:

=AVERAGE(SMALL(IF($A$1:$A$13=D1,IF($B$1:$B$13<>"",$B$1:$B$13)),{1,2,3}))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article
<[email protected]>,
stuffandthings
<[email protected]> wrote:

> Here's the basic set up:
>
> A| B
> 1| 456
> 1| 52
> 1| 78
> 1| 91
> 1| 12
> 1| 6
> 1| 7
> 2| 12
> 2| 80
> 2| 9
> 2| 15
> 2| 12
> 2| 6
>
>
> And here's the question:
> Basically, column A represents an item # and column B represent the
> item price.
>
> What I want to do is - and please know I'm sincere when I say I've
> really been trying to solve this myself - every time A:A = 1, I want to
> average the lowest 3 prices in B:B relating to A:A1... and then average
> the lowest 3 prices in B:B relating to the A:A = 2.
>
> Maybe I've completely over-thought this one.
>
> Keep in mind, too... that this is a really basic example of what I'm
> trying to do. There are over 1400 individual items, each being sold at
> multiple prices. I understand enough of excel that a similarly basic
> example will suffice - one that I can just create 1400+ times over.
>
> Thanks to anyone ahead of time for your help.
>
> Stuffandthings