I'm trying to find specific set values without space for some calculations witch I need, but by using the indirect function gives me a wrong result "E4:G4", when convert this Array to Values array By pressing F9 works well "E3:G3"
what About using Mid function
what About using Mid function
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Grp | Val | AAA | AAA | AAA | ||||
2 | AAA | 43831 | -5 | = | |||||
3 | BBB | 43466 | By Pressing "F9" => | 43831 | 43826 | 43826 | |||
4 | CCC | 44562 | 43831 | 43826 | #VALUE! | ||||
5 | DDD | 45658 | |||||||
6 | AAA | 43832 | |||||||
7 | CCC | 44563 | |||||||
8 | AAA | 43833 | |||||||
9 | DDD | 45659 | |||||||
10 | CCC | 44564 | |||||||
11 | AAA | 43834 | |||||||
12 | AAA | 43835 | |||||||
13 | AAA | 43836 | |||||||
14 | BBB | 43467 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3 | E3 | ={43831;43832;43833;43834;43835;43836} |
F3 | F3 | ={43831;43832;43833;43834;43835;43836}-5 |
G3 | G3 | ={43826;43827;43828;43829;43830;43831} |
E4 | E4 | =INDIRECT(ADDRESS(AGGREGATE(15,6,ROW(INDIRECT("2:"&ROWS($A$2:$A$14)))/($A$2:$A$14=E$1),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$14,"AAA")))),2)) |
F4 | F4 | =INDIRECT(ADDRESS(AGGREGATE(15,6,ROW(INDIRECT("2:"&ROWS($A$2:$A$14)))/($A$2:$A$14=F$1),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$14,"AAA")))),2))-5 |
G4 | G4 | ={#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} |