Forum Discussion
Excel Formula Help
I have a table listing of streets with house number ranges. I want to list each street as a single entry along with the lowest house number and highest house number on that street. There may be several rows of the same street, each with a different range Lowest - Highest on LH Side of Street; Lowest to Highest on RH side of Street.
See attached XLSX
Ok, If you do not have 365 then the below two formula will work in any version of Excel
both formulas below are array formula. so it cannot work if it is just entered.
when you enter the formula, Plz hold keyboard CONTROL SHIFT & ENTER.
For lowest put this and drag down=MIN(IF($A$4:$A$14=A18,$B$4:$B$14))
and for highest put below formula and drag down=MAX(IF($A$4:$A$14=A18,$B$4:$B$14))
If you do not like the control shift enter.
then if you have Excel 2010 or above then the following formulas will work just without the need of special keystroke.
For lowest
=AGGREGATE(14,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)
for Highest
=AGGREGATE(15,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)
Mark,
F18 =MINIFS($B$4:$B$14,$A$4:$A$14,A18) G18 =MAXIFS($E$4:$E$14,$A$4:$A$14,A18) - The result comes up with #NAME? It appears this only works in Office 365. Unfortunately the company I work for does not have this version of Excel software.
Ok, If you do not have 365 then the below two formula will work in any version of Excel
both formulas below are array formula. so it cannot work if it is just entered.
when you enter the formula, Plz hold keyboard CONTROL SHIFT & ENTER.
For lowest put this and drag down=MIN(IF($A$4:$A$14=A18,$B$4:$B$14))
and for highest put below formula and drag down=MAX(IF($A$4:$A$14=A18,$B$4:$B$14))
If you do not like the control shift enter.
then if you have Excel 2010 or above then the following formulas will work just without the need of special keystroke.
For lowest
=AGGREGATE(14,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)
for Highest
=AGGREGATE(15,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)
