Below I have a spreadsheet with Dairy Data: The bottom half consists of data that I receive as .csv raw data (copied and pasted in daily) and the top half is a table with formulae to average that data. I only need an average for 3 rows of data. An average for counting all the rows whose Cow type is 1, An average for counting all the rows whose Cow type is 2, and finally (and this is my problem) an average for all Cow types combined from 3 to 11 after this.
Cow types 1 and 2 contain this formula below (depending on the cell it’s in) as follows – and it works fine: =IFERROR(AVERAGEIFS(C$11:C$5000,$B$11:$B$5000,$B2,C$11:C$5000,">0"),"")
It works individually for rows 3 to 11 but I don’t want that. I need Cow type 1,2 and 3+. 3 rows of formulae.
Important Note: 0’s and blanks must be ignored and not included in the count. There can be up to 5000 cows at any one time!! (5000 rows of raw data).
TOP HALF with FORMULAE
<tbody>
</tbody>
Can someone help me with this?
Cow types 1 and 2 contain this formula below (depending on the cell it’s in) as follows – and it works fine: =IFERROR(AVERAGEIFS(C$11:C$5000,$B$11:$B$5000,$B2,C$11:C$5000,">0"),"")
It works individually for rows 3 to 11 but I don’t want that. I need Cow type 1,2 and 3+. 3 rows of formulae.
Important Note: 0’s and blanks must be ignored and not included in the count. There can be up to 5000 cows at any one time!! (5000 rows of raw data).
TOP HALF with FORMULAE
| Cow Type | % Milk T1 | % Weight T1 | % Conc T1 | % Milk T1 | % Weight T1 | % Conc T1 | |
| COW TYPE AVERAGES | 1 | 52% | 56% | 52% | 56% | 52% | 56% |
| 2 | 52% | 50% | 52% | 50% | 52% | 50% | |
| 3 | 10% | 50% | 10% | 50% | 10% | 50% | |
| 4 | | 20% | | 20% | | 20% | |
| 5 | | | | | | | |
| 6 | 75% | 50% | 75% | 50% | 75% | 50% | |
| 7 | | | | | | | |
| etc | BOTTOM | HALF | |||||
| RAW DATA | Individual Cow Types | Individual Milk T1 %'s | Individual Wt T1 %'s | Individual Conc T1 %'s | Individual Milk T2 %'s | Individual Wt T1 %'s | Individual Conc T1 %'s |
| 1 | 40% | 30% | 40% | 30% | 40% | 30% | |
| 1 | 40% | 70% | 40% | 70% | 40% | 70% | |
| 1 | 0% | 100% | 0% | 100% | 0% | 100% | |
| 2 | 40% | | 40% | | 40% | | |
| 2 | 40% | 70% | 40% | 70% | 40% | 70% | |
| 2 | 75% | 30% | 75% | 30% | 75% | 30% | |
| 1 | 75% | 25% | 75% | 25% | 75% | 25% | |
| 4 | | 20% | | 20% | | 20% | |
| 5 | | | | | | | |
| 6 | 75% | | 75% | | 75% | | |
| 3 | 10% | 50% | 10% | 50% | 10% | 50% | |
| 6 | 0% | 50% | 0% | 50% | 0% | 50% |
<tbody>
</tbody>
Can someone help me with this?
!
