How to work out average number of eggs that developed, laid per day, for breeding pairs

Hi,
I have a few things that I would like to be able to do with the attached spreadsheet 'Egg data', which I hope you can please help me with. The main thing that I would like to be able to do is to work how many eggs that developed were laid per day for each of the pairs. To do this I need to be able to somehow calculate many days that each pair was laying. So for example pair 1 laid over 3 days, pair 4 over 4 days etc. I thought there might be a function that counts how many distinct values there are in a column, but I could not find anything that does this. I also need to have something that will sum the values in the developed egg column for each of the different pairs, on the spreadsheet I have given an example of the output I want for the first 4 pairs, see column D) (I want this for all pairs), I would like a formula that I can paste at the top of the column and just drag down that will do this automatically. If the number of days each pair was laying over could also be added in to the spreadsheet in the same way (see column E) then I could work out the average amount of eggs laid that developed per day for each of the pairs by dividing column D by column E (though if anyone knows a better way to do this please let me know). Also if there is a more efficient way of filling in the 'Pair ID, Average number of eggs laid that developed' table than just copying and pasting for each pair, please let me know. I find hard to describe what I want as I am not that computer literate or mathmatically minded, so I do know the terms for everything. I really appreciate help with this and please let me know if you need anymore info.
Thanks and all the best
Laura

Laura,

Based on sample data you might try something like:

Formula:

Please Login or Register  to view this content.

so for pair1 you would get 0&3, pair2 you would get 6&5 etc.

note: I've assumed you have ; delimiter {Sweden} but if not change ; to ,

I may have misunderstood... if you just want to populate the table then, assuming you're using a recent version of XL

Formula:

Please Login or Register  to view this content.

if your dataset is much larger than your sample then it would make sense to restrict the ranges in the SUMPRODUCT to better utilise fact data is sorted (by Pair).