Please sort my parrots

May 21, 2008 5:22 AM

Item: one Excel spreadsheet. Contains: fifty rows. Column A - parrot name. Column B - parrot selling price. Column C - parrot age when sold. Without sorting the rows, how can I find the average age when sold of the most expensive ten parrots? I know I need an array here but just can't work out how to get my correct parrot average.
posted by humuhumu to computers & internet (10 comments total)

Hey there,

lets say if you've got 100 rows in your column for 100 prices and ages

you can use

=rank(B1, $B$1:$B$100,0)

which will give each row a rank based on its rank in among the prices in the price column. you have to copy that down to give a ranking for each of the different rows. lets say in Column D

then you'll have a ranking for each of the different parrots which should be from 1 to 10 for the top ten. changing the 0 to 1 in the formula above puts it the other way around.

then, its an array function.

=AVERAGE(IF(D1:D16<> (and then as you probably know to make it an array formula, press F2 and then control=shift-return).

which gives you the average of ages if the ranking is 10 or below. as long as i've done that alright
posted by galactain at 5:47 AM on May 21, 2008

unbelievable. the array function is

=average(if(D1:D100<>
sorry about that. looks ok in preview. i'll try it again
posted by galactain at 5:49 AM on May 21, 2008

ok. sorry about this. don't know what's going wrong.

but its =average(if(d1:d100 and then

(is greater than or equal to) 10, C1:C100,"")

if that doesn't work, then you've probably figured it out. its the average of the ages, if the ranking is below 10. sorry about the mess.
posted by galactain at 5:52 AM on May 21, 2008

A spreadsheet is not the right tool for the job, often. Dump it into Access (or MySQL, natch) and "SELECT AVG(age) FROM (SELECT age FROM t ORDER BY price DESC LIMIT 10)".
posted by cmiller at 5:53 AM on May 21, 2008

Can I ask a stupid question - why can't you sort the rows? If you want to preserve the existing sheet as it is, right click on the sheet tab, choose Move Or Copy, check Create a Copy.

Now you have a new sheet with the same info. Sort the sheet on the price, highest first. Do a regular average formula on the age column for the top 10 parrots.
posted by rhys at 6:03 AM on May 21, 2008

This ought to work
=average(if(c1:c50>=large(c1:c50,10),c1:c50))
and ctrl+shift+enter
posted by milkrate at 7:27 AM on May 21, 2008

For the average age, it should be
=average(if(c1:c50>=large(c1:c50,10),b1:b50))
and ctrl+shift+enter
posted by milkrate at 7:41 AM on May 21, 2008

Or, to be actually correct
=average(if(b1:b50>=large(b1:b50,10),c1:c50)), ctrl+shift+enter
posted by milkrate at 7:42 AM on May 21, 2008

milkrate, perfect. Thanks!

galactain, thanks also but I would have preferred not to add a rank and then sort.

Rhys, I have a lot of metaphorical parrots from a lot of dates and need to do this automatically so would rather not produce copies when i want to work out the figs.
posted by humuhumu at 8:23 AM on May 21, 2008

You just had to disappoint us all by letting on that the parrots are only metaphorical, didn't you.
posted by dmd at 9:11 PM on May 21, 2008 [1 favorite has favorites]