Combining two formulas

B

Bruno Campanini

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(OFFSET(_N,,,ROW(_N)-CELL("row",_N)+1),_N)=1,COUNTIF(_N,"<"&_N)),ROW(INDIRECT(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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

array formulas using MIN MAX 3
Changing entire row based on 1 MAX cell value (Conditional Formatt 2
Using icon sets with relative references 0
Conditional formatting 2
Help with formula 0
Complicated vlookup/min-max query...please help 6
Combining formulas does not work 3
"search" a different sheet with multiple criteria 2

Top