| Sample.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Sample1 | Product 1 | Product 2 | Product 3 | Diff | Average below 0 | |||
| 2 | $6.85 | $10.76 | 57.08% | -18.13% | |||||
| 3 | $11.75 | $7.55 | -35.74% | Average above 0 | |||||
| 4 | $3.58 | $3.22 | -9.93% | 34.87% | |||||
| 5 | $3.70 | $3.49 | -5.68% | ||||||
| 6 | $8.28 | $5.19 | -37.28% | Average if less than 15 including negative numbers | |||||
| 7 | $7.50 | $8.45 | 12.67% | -2.98% | |||||
| 8 | $2.50 | $2.19 | $2.45 | -2.00% | Should be 12.99 | ||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2 | G2 | =AVERAGE(FILTER(E2:E8,(E2:E8<=0)*SUBTOTAL(2,OFFSET(E1,SEQUENCE(ROWS(E2:E8)),0)))) |
| G4 | G4 | =AVERAGE(FILTER(E2:E8,(E2:E8>=0)*SUBTOTAL(2,OFFSET(E1,SEQUENCE(ROWS(E2:E8)),0)))) |
| G7 | G7 | =AVERAGE(FILTER(E2:E8,(E2:E8<=15)*SUBTOTAL(2,OFFSET(E1,SEQUENCE(ROWS(E2:E8)),0)))) |
| E2:E8 | E2 | =IFERROR(IF(D2,(D2-B2)/B2,(C2-B2)/B2),"") |
So I am able to average column E either using the negative or positive values with the 2 separate formulas I have in cell G2 and G4. Formula working as intended
BUT
when I want to average up only cells in column E that is less than 15, the formula calculates up the total column instead.
For this particular criteria, i am trying to achieve averaging cells that is "less than 15" from column E, which includes BOTH negative and positive numbers. For my particular sample, this would EXCLUDE E2 (since 57.08 is greater than 15) but INCLUDES E7 (12.67 less than 15) AND the other cells with the negative value since they are also technically less than 15. The end result SHOULD be 12.99 but instead its showing 2.98 averaging up the whole column instead.
Can someone please advise me on what I am missing or doing wrong about the formula in G7 that is averaging up the whole column instead of what I am trying to achieve? Thank you in advance
