Mar 21 2018
11:51 AM
- last edited on
Jul 25 2018
11:24 AM
by
TechCommunityAP
Mar 21 2018
11:51 AM
- last edited on
Jul 25 2018
11:24 AM
by
TechCommunityAP
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
Mar 21 2018 02:36 PM
Mark,
F18 | =MINIFS($B$4:$B$14,$A$4:$A$14,A18) |
G18 | =MAXIFS($E$4:$E$14,$A$4:$A$14,A18) |
Mar 22 2018 05:44 AM
Mar 22 2018 06:12 AM - edited Mar 22 2018 06:18 AM
SolutionOk, 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)
Mar 22 2018 06:12 AM - edited Mar 22 2018 06:18 AM
SolutionOk, 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)