Hello,
In the attached, I try to get the max price (cell E2) and to filter (cell A7) according to the selection of group and sub group.
While with a specific subgroup it works fine, when I select the "all" option (in B2), it doesn't work.
Can someone help me to find what is not working right in my formulas?
Thx
Nati
In the attached, I try to get the max price (cell E2) and to filter (cell A7) according to the selection of group and sub group.
While with a specific subgroup it works fine, when I select the "all" option (in B2), it doesn't work.
Can someone help me to find what is not working right in my formulas?
Thx
Nati
excel_question.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | group to choose | subgroup to choose | total price | items | max price | average | ||
2 | group2 | subgroup26 | 138,032.73 | 7 | 23,928.13 | 19,718.96 | ||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | cat_num | data1 | data2 | code | price | |||
7 | 2104 | data49 | data59 | 22800 | 17305.95 | |||
8 | 2711 | data207 | data217 | 22800 | 23928.13 | |||
9 | 2732 | data217 | data227 | 22800 | 23928.13 | |||
10 | 2753 | data227 | data237 | 22800 | 23928.13 | |||
11 | 2809 | data245 | data255 | 22800 | 19988.83 | |||
12 | 2858 | data264 | data274 | 22800 | 22367.6 | |||
13 | 8038 | data676 | data686 | 22800 | 6585.96 | |||
stats |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,IFERROR(INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1),""), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$61,0)+16, Last_unit_row,SUMPRODUCT(MAX(ROW(parameters!$B:$B)*(parameters!$B:$B<>""))), Old_salary_col,Total_sheet!$E:$E, Saif_col,Total_sheet!$D:$D, All_units,IF(Main_next_unit_name="",SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Last_unit_row))),SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Main_next_unit_row-1)))), One_unit,SUM(SUMIFS(Old_salary_col,Saif_col,INDIRECT("parameters!D"&Unit_row&":o"&Unit_row))), IF($B$2="all",All_units,One_unit)) |
D2 | D2 | =LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,IFERROR(INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1),""), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$61,0)+16, Last_unit_row,SUMPRODUCT(MAX(ROW(parameters!$B:$B)*(parameters!$B:$B<>""))), Old_salary_col,Total_sheet!$E:$E, Saif_col,Total_sheet!$D:$D, All_units,IF(Main_next_unit_name="",SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Last_unit_row))),SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Main_unit_row+1&":O"&Main_next_unit_row-1)))), One_unit,SUM(COUNTIFS(Saif_col,INDIRECT("parameters!D"&Unit_row&":o"&Unit_row))), IF($B$2="all",All_units,One_unit)) |
E2 | E2 | =LET( Start_row,16, find_row,MATCH($B$2,parameters!$B$17:$B$51,0)+Start_row, MAX(IF(Total_sheet!$D:$D=INDIRECT("parameters!$D"&find_row&":$O"&find_row),Total_sheet!E:E))) |
F2 | F2 | =$C$2/$D$2 |
A7:E13 | A7 | =LET( Main_unit_row,MATCH($A$2,parameters!$A$16:$A$61,0)+15, Main_next_unit_name,INDEX(parameters!$A$3:$F$3,MATCH($A$2,parameters!$A$3:$F$3,0)+1), Main_next_unit_row,MATCH(Main_next_unit_name,parameters!$A$16:$A$61,0)+15, Unit_row,MATCH($B$2,parameters!$B$17:$B$52,0)+16, Saif_col,Total_sheet!$D:$D, Unit_seifim_all,INDIRECT("parameters!$d"&Main_unit_row_row&":$o"&Main_next_unit_row), Unit_seifim1,INDIRECT("parameters!$d"&Unit_row&":$o"&Unit_row), All_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim1,0))),{1,1,1,1,1}), One_unit,FILTER(FILTER(Total_sheet!$A:$E,ISNUMBER(MATCH(Saif_col,Unit_seifim1,0))),{1,1,1,1,1}), IF($B$2="all",All_unit,One_unit)) |
Dynamic array formulas. |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A2 | List | =parameters!$A$3:$F$3 |
B2 | List | =OFFSET(parameters!$A$3,1,MATCH($A2,parameters!$A$3:$F$3,0)-1,COUNTA(OFFSET(parameters!$A$3,1,MATCH($A2,parameters!$A$3:$F$3,0)-1,15)),1) |