How to average a specific entry

[billsyr814]

I have a large spreadsheet with 20 or 30 different entries. I would like to create an average for each entry to use when that particular entry comes up as a new item. For example if I had a long list of fruit and I was going to enter another Apple I would like the spreadsheet look up all the apples and give me an average weight any suggestions on how to create this thank you

 

[WaterGypsy]

I'd probably "cheat" by putting a formula in the weight column to calculate the average when you enter the fruit .... then overwrite it if needed ... this is a messy solution as it requires the column to be populated ahead of use but should give the result you want.

=IF(A9="","",SUMIFS(B$2:B8,A$2:A8,A9)/COUNTIFS(A$2:A8,A9))

This is an example I used to check it works .... but I am sure someone will be along to explain how to do it with a change procedure

 

[DaBoggs]

I did similar to WaterGypsy and used a simple approach. Let say you have columns A through E with Fruit, Quantity, Unit Weight, Total Weight, & Historical Unit Weight. Columns A & B are obviously just input and C & D could be done either way whether you weighed individually and average or weight a bag of 10 and averaged it to get the unit weight. Sorry, I'm just trying to make some assumptions here so I can setup a sheet and formula.

Cell E2 Formula =SUMIF(A:A,A2,D:D)/SUMIF(A:A,A2,B:B)

Basically just sum the total weight and divide it by the total units.

 

[billsyr814]

These are great approaches. I’ll give it a try!!! Thx so much.

 

[billsyr814]

client	total exam time	aver time for this exam	start time	Actual end time	actual length of exam	ESTIMATED END TIME
NYS TEACHERS	4:30	3:0	8:00	10:30	2:30	11:00
NCLEX	6:40	2:40	7:50	9:00	1:10
AAMC	7:30	6:40	8:21	14:3	:09
NCLEX	6:40	? (Based on all previous NCLEX exam in db need formula here	8:00	---	---	avr test time added to start time

<tbody>
</tbody>

I did similar to WaterGypsy and used a simple approach. Let say you have columns A through E with Fruit, Quantity, Unit Weight, Total Weight, & Historical Unit Weight. Columns A & B are obviously just input and C & D could be done either way whether you weighed individually and average or weight a bag of 10 and averaged it to get the unit weight. Sorry, I'm just trying to make some assumptions here so I can setup a sheet and formula.

Cell E2 Formula =SUMIF(A:A,A2,D:D)/SUMIF(A:A,A2,B:B)

Basically just sum the total weight and divide it by the total units.

--------------------------------------------------------------------------------------------------------

I created a spread sheet above to help demonstrate my exact problem.
What I do now is data sort by exam and manually find the average exam length.

Looking for a formula to do that for me.
Thanks

/bill