Aces High Bulletin Board
General Forums => The O' Club => Topic started by: rabbidrabbit on October 22, 2009, 12:37:42 PM
-
OK, It's been a while so could anyone help me dial in an excel issue?
I have a number of cells (F4-53 - J4-53) which have weights of fish entered for an event we held. I already have the total weight but would like the total number of fish turned in to generate an average weight. Cells where no fish were donated are either empty or have 0.00 in them. I can delete the 0.00 entries or make the blanks 0.00 if that helps.
How do I go about this?
-
what excell are you using?
-
http://office.microsoft.com/en-us/excel/HP030561351033.aspx#Calculate%20a%20weighted%20average
-
http://office.microsoft.com/en-us/excel/HP030561351033.aspx#Calculate%20a%20weighted%20average
There you go. that should answer your quesion
-
Unless I misread your link, I think I want: =AVERAGE(IF(F4:F50<>0, F4:F50,""))
As it takes the average while disregarding zeros. However, I'm still trying to figure out the syntax for adding the f-j columns.
I'm also still searching for a Total Number of Fish Turned in calculation from the same columns as well.
I'll keep looking. It's been 15 years since I spent quality time with Excel some I'm pretty rusty. Anyone have some pointers?
-
So column F, H, I, J are all fish weights? With cells that don't have a fish weight either blank or 0.0? So you need to cound how many cells in F,H,I,J have valid data and then sum the total weight and divide by the number of cells with content, is that correct?
-
If my above statement was correct then
Then average weight would be =sum(F4:J50)/countif(F4:J50,">0")
-
So column F, H, I, J are all fish weights? With cells that don't have a fish weight either blank or 0.0? So you need to cound how many cells in F,H,I,J have valid data and then sum the total weight and divide by the number of cells with content, is that correct?
Correct, Columns F,G,H,I,J all have weights entered with the exception of teams that weighed in less than 5 fish. Those places are currently either blank or have an entry of 0.00. Ideally, the calculation would ignore the blanks and 0.00 and output the average weight of all the fish weighed.
A second calculation is needed to count each cell with a number in it an output the total number of fish caught, not the total weight which I already have.
The summation should be: Total Weight of Fish Weighed = already have
Total Number of Fish Weighed =
Average Weight of Fish Weighed=
There were and additional 78 tuna donated but were unweighed. By getting an average of the fish weighed we can extend that out to the additional fish donated to calculate roughly the total weight of all fish turned in, weighed or not, so we have a estimate of the total weight of fish donated to the food bank.
-
countif(RANGE,criteria) should give you Total # fish. ie =countif(f4:j50,">0") should result in # of fish.
Then Total Lbs / Total # should = average.
-
That's pretty much it. Thanks!