Hi everyone,
I need assistance on how to correctly reference the dynamic array in the example below. I am using Excel 2021.
The formulas in L2 and M2, give me the maximum and minimum price that each customer paid. When I drag down, the formula works as intended. However, when I change the formula to reference the array by adding the spill range reference, as highlighted below, I get an #N/A error.
=MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2#)*(Trades[ITEM]=J2#)))
I need assistance on how to correctly reference the dynamic array in the example below. I am using Excel 2021.
The formulas in L2 and M2, give me the maximum and minimum price that each customer paid. When I drag down, the formula works as intended. However, when I change the formula to reference the array by adding the spill range reference, as highlighted below, I get an #N/A error.
=MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2#)*(Trades[ITEM]=J2#)))
ITEM | SALES PRICE | ORDER SIZE | ZONE | CUSTOMER ID |
TIMBER | 15.7 | 100 | WEST | 27982 |
BOLTS | 15.1 | 100 | EAST | 9870 |
TIMBER | 15.75 | 600 | WEST | 35861 |
TIMBER | 15.3 | 300 | WEST | 27464 |
BOLTS | 15.75 | 2530 | WEST | 5175 |
TIMBER | 15.45 | 200 | WEST | 20615 |
TIMBER | 15.75 | 100 | WEST | 30667 |
BOLTS | 15.5 | 500 | EAST | 9179 |
TIMBER | 15.75 | 1000 | WEST | 1512 |
BOLTS | 15.4 | 500 | EAST | 5175 |
TIMBER | 15.75 | 1000 | WEST | 31720 |
TIMBER | 15.5 | 7100 | WEST | 21344 |
NAILS | 15.2 | 200 | WEST | 20615 |
NAILS | 15 | 500 | WEST | 20615 |
TIMBER | 15 | 1000 | WEST | 31720 |
TIMBER | 15 | 2500 | WEST | 32119 |
BOLTS | 15.9 | 500 | EAST | 9870 |
TIMBER | 15 | 1000 | WEST | 1512 |
BOLTS | 15.3 | 100 | WEST | 9870 |
BOLTS | 15.3 | 300 | WEST | 9179 |
TIMBER | 15.35 | 100 | WEST | 20615 |
BOLTS | 15.5 | 300 | WEST | 20615 |
NAILS | 21.2 | 400 | WEST | 1834 |
Customer Reconciliation.xlsx | |||||||
---|---|---|---|---|---|---|---|
I | J | K | L | M | |||
1 | CUSTOMER ID | ITEM | TOTAL ORDERS | HIGHEST PRICE | LOWEST PRICE | ||
2 | 9870 | BOLTS | 600 | 15.9 | 15.1 | ||
3 | 9179 | BOLTS | 500 | ||||
4 | 5175 | BOLTS | 500 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I4 | I2 | =UNIQUE(FILTER(Trades[CUSTOMER ID],(Trades[ITEM]="BOLTS")*(Trades[ZONE]="EAST"))) |
J2:J4 | J2 | =IF(ISNUMBER(I2#),"BOLTS",0) |
K2:K4 | K2 | =SUMIFS(Trades[ORDER SIZE],Trades[CUSTOMER ID],"="&I2#,Trades[ITEM],"="&J2#,Trades[ZONE],"EAST") |
L2 | L2 | =MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2))) |
M2 | M2 | =MIN(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2))) |
Dynamic array formulas. |