![]() |
Combining two formulas
Given two ranges:
_N, names (string), (A1:A16) _V, values (number), (B1:B16) the following: {=IF(ROW(A1)<=SUM(IF(LEN(_N)0,1/COUNTIF(_N,_N))),INDEX(_N,MATCH(SMALL(IF(COUNTIF(O FFSET(_N,,,ROW(_N)-CELL("row",_N)+1),_N)=1,COUNTIF(_N,"<"&_N)),ROW(IN DIRECT(ROW(A1)&":"&ROWS(_N)))),COUNTIF(_N,"<"&_N), 0)),"")} FormulaArray, C1 down, groups and sorts names. the following: {=MAX((_N=C1)*_V)} FormulaArray, D1 down, gives Max of values for each name the following: {=MIN(IF((_N=C1),(_N=C1)*_V,MAX(_V)))} FormulaArray, E1 down, gives the Min of values for each name How can I get one only formula to spread in a (n rows) x (3 columns) to show Names (unique ed ordered), Max values, Min values? Bruno |
All times are GMT +1. The time now is 09:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com