Forum Discussion
IF, AND, AVERAGE help!
Hi All,
I am really stuck and would very much appreciate some help, please!
I need to find the average of a range of data when there are specific "material" types in two different columns.
For example, I want to know if Material A is in range A1:A10 and Material B is in B1:B10 then average the related data in range C1:C10... does that make sense?
Here is an excerpt below of real data... I want to find the average where the secondary material is 'Concrete Fines' has been used with the Primary Material 'Type 1 LS' and find the average of the % Used.
I need to do this for all the different types of Primary and Secondary Material which may be mixed.
| Date | Secondary Material | Cheaper Tonnes | Primary Material | Primary Tonnes | Total | % Used |
| 2 September 25 | Concrete Fines | 14.7 | Type 1 LS | 144.84 | 159.54 | 10.15% |
| 2 September 25 | Concrete Fines | 45.4 | 6F5 LS | 441.22 | 486.62 | 10.29% |
| 2 September 25 | Concrete Fines | 44.4 | 40mm Scalps | 425.46 | 469.86 | 10.44% |
| 4 September 25 | Concrete Fines | 7.2 | Type 1 LS | 143.9 | 151.10 | 5.00% |
| 4 September 25 | Concrete Fines | 58.3 | 40mm Scalps | 593.44 | 651.74 | 9.82% |
| 4 September 25 | Concrete Fines | 27 | 6F5 LS | 294.32 | 321.32 | 9.17% |
| 5 September 25 | Concrete Fines | 16.2 | Type 1 LS | 148.02 | 164.22 | 10.94% |
| 5 September 25 | Concrete Fines | 42.2 | 6F5 LS | 443.38 | 485.58 | 9.52% |
Thank you!
Jess
1 Reply
As variant
=GROUPBY(HSTACK(Table1[Secondary Material],Table1[Primary Material]),Table1[% Used], AVERAGE,,0)
