Get min value in floating range

K

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
607
Office Version
  1. 2013
Platform
  1. Windows
I am trying to find the min price in column E, in column G there is an indicator of when the range changes, the X in column H is my manual checkmark to show the lowest price, what i would like is to have a formula to show the lowest price instead of my manual method.
Test Sheet 4-12-21.xlsb
DEFGHI
21. Del Bocavista Bay2.541.751x
32. Ourfirstimpression2.761.481
43. Funky Music5.492.381
54. The Whizzer11.484.121
65. Allamerican Teen36.528.601
71. Bathtime1000.0013.052
810. Rockandrollartist35.672.092
92. Anotherchook860.00483.752
103. Gota Good Warhol320.004.812
114. Pink Parade116.703.692
125. Galeocerdo Cuvier434.7613.542
136. Showpony1.111.042x
147. Oh So Grand191.496.702
158. Dazzling Jewel106.064.062
169. Jades Fortune38.051.942
171. Batavia Streamline12.002.923
1810. Shes A Harleequinn10.412.533
192. Bettor Look33.626.513
203. Dynamite Dolly34.666.463
214. Twentynine Twelve7.802.493
225. Keptain Courageous2.511.413x
236. Wattabout Rioli7.402.503
247. Alta Camino29.255.983
258. Highly Flammable19.514.243
269. Lorinda Kate213.1016.993
Back Win Lay Place
Cell Formulas
RangeFormula
D2:D26D2=IF(U2="","",U2)
E2:E26E2=IF(W2="","",W2)
F2:F26F2=IF(Y2="","",Y2)
G3:G26G3=IF(R3="","",IF(R2=R3,G2,G2+1))
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
klatlap said:
I am trying to find the min price in column E, in column G there is an indicator of when the range changes, the X in column H is my manual checkmark to show the lowest price, what i would like is to have a formula to show the lowest price instead of my manual method.
Test Sheet 4-12-21.xlsb
DEFGHI
21. Del Bocavista Bay2.541.751x
32. Ourfirstimpression2.761.481
43. Funky Music5.492.381
54. The Whizzer11.484.121
65. Allamerican Teen36.528.601
71. Bathtime1000.0013.052
810. Rockandrollartist35.672.092
92. Anotherchook860.00483.752
103. Gota Good Warhol320.004.812
114. Pink Parade116.703.692
125. Galeocerdo Cuvier434.7613.542
136. Showpony1.111.042x
147. Oh So Grand191.496.702
158. Dazzling Jewel106.064.062
169. Jades Fortune38.051.942
171. Batavia Streamline12.002.923
1810. Shes A Harleequinn10.412.533
192. Bettor Look33.626.513
203. Dynamite Dolly34.666.463
214. Twentynine Twelve7.802.493
225. Keptain Courageous2.511.413x
236. Wattabout Rioli7.402.503
247. Alta Camino29.255.983
258. Highly Flammable19.514.243
269. Lorinda Kate213.1016.993
Back Win Lay Place
Cell Formulas
RangeFormula
D2:D26D2=IF(U2="","",U2)
E2:E26E2=IF(W2="","",W2)
F2:F26F2=IF(Y2="","",Y2)
G3:G26G3=IF(R3="","",IF(R2=R3,G2,G2+1))
Click to expand...
when copying your mini worksheet we need column U. As we only get the formulas in the range your pasted. can you hide unnecessary columns between H and R, S to U? and repaste? Please.
 
Upvote 0
klatlap said:
Column U only contains text, the same as E and F
Click to expand...
This is what I get when I paste your data in:
Not sure how to tackle your question with nothing.
1675554103491.png
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Does this work for you?

23 02 05.xlsm
DEFGH
21. Del Bocavista Bay2.541.751x
32. Ourfirstimpression2.761.481 
43. Funky Music5.492.381 
54. The Whizzer11.484.121 
65. Allamerican Teen36.528.61 
71. Bathtime100013.052 
810. Rockandrollartist35.672.092 
92. Anotherchook860483.752 
103. Gota Good Warhol3204.812 
114. Pink Parade116.73.692 
125. Galeocerdo Cuvier434.7613.542 
136. Showpony1.111.042x
147. Oh So Grand191.496.72 
158. Dazzling Jewel106.064.062 
169. Jades Fortune38.051.942 
171. Batavia Streamline122.923 
1810. Shes A Harleequinn10.412.533 
192. Bettor Look33.626.513 
203. Dynamite Dolly34.666.463 
214. Twentynine Twelve7.82.493 
225. Keptain Courageous2.511.413x
236. Wattabout Rioli7.42.53 
247. Alta Camino29.255.983 
258. Highly Flammable19.514.243 
269. Lorinda Kate213.116.993 
Min
Cell Formulas
RangeFormula
H2:H26H2=IF(E2=MINIFS(E$2:E$26,G$2:G$26,G2),"x","")


@awoohaw
You don't need column U. (You also are best to paste into the same range the mini sheet came from. That is D2 as top-left cell in this case.)

1675555743676.png
 
Upvote 0
Peter_SSs said:
Does this work for you?
Click to expand...
If you don't have the MINIFS function

23 02 05.xlsm
DEFGH
21. Del Bocavista Bay2.541.751x
32. Ourfirstimpression2.761.481 
43. Funky Music5.492.381 
54. The Whizzer11.484.121 
65. Allamerican Teen36.528.61 
71. Bathtime100013.052 
810. Rockandrollartist35.672.092 
92. Anotherchook860483.752 
103. Gota Good Warhol3204.812 
114. Pink Parade116.73.692 
125. Galeocerdo Cuvier434.7613.542 
136. Showpony1.111.042x
147. Oh So Grand191.496.72 
158. Dazzling Jewel106.064.062 
169. Jades Fortune38.051.942 
171. Batavia Streamline122.923 
1810. Shes A Harleequinn10.412.533 
192. Bettor Look33.626.513 
203. Dynamite Dolly34.666.463 
214. Twentynine Twelve7.82.493 
225. Keptain Courageous2.511.413x
236. Wattabout Rioli7.42.53 
247. Alta Camino29.255.983 
258. Highly Flammable19.514.243 
269. Lorinda Kate213.116.993 
Min
Cell Formulas
RangeFormula
H2:H26H2=IF(E2=AGGREGATE(15,6,E$2:E$26/(G$2:G$26=G2),1),"x","")
 
Upvote 0
Solution
Test Sheet 4-12-21.xlsb
DEFGSUWY
1MENU_HINT2SELECTION_NAMEBSP_WinBSP_Place
21. Del Bocavista Bay2.541.751AUS / Albany (AUS) 3rd Feb1. Del Bocavista Bay2.541.75
32. Ourfirstimpression2.761.481AUS / Albany (AUS) 3rd Feb2. Ourfirstimpression2.761.48
43. Funky Music5.492.381AUS / Albany (AUS) 3rd Feb3. Funky Music5.492.38
54. The Whizzer11.484.121AUS / Albany (AUS) 3rd Feb4. The Whizzer11.484.12
65. Allamerican Teen36.528.601AUS / Albany (AUS) 3rd Feb5. Allamerican Teen36.528.60
71. Bathtime1000.0013.052AUS / Albany (AUS) 3rd Feb1. Bathtime1000.0013.05
810. Rockandrollartist35.672.092AUS / Albany (AUS) 3rd Feb10. Rockandrollartist35.672.09
92. Anotherchook860.00483.752AUS / Albany (AUS) 3rd Feb2. Anotherchook860.00483.75
103. Gota Good Warhol320.004.812AUS / Albany (AUS) 3rd Feb3. Gota Good Warhol320.004.81
114. Pink Parade116.703.692AUS / Albany (AUS) 3rd Feb4. Pink Parade116.703.69
125. Galeocerdo Cuvier434.7613.542AUS / Albany (AUS) 3rd Feb5. Galeocerdo Cuvier434.7613.54
136. Showpony1.111.042AUS / Albany (AUS) 3rd Feb6. Showpony1.111.04
147. Oh So Grand191.496.702AUS / Albany (AUS) 3rd Feb7. Oh So Grand191.496.70
158. Dazzling Jewel106.064.062AUS / Albany (AUS) 3rd Feb8. Dazzling Jewel106.064.06
169. Jades Fortune38.051.942AUS / Albany (AUS) 3rd Feb9. Jades Fortune38.051.94
171. Batavia Streamline12.002.923AUS / Albany (AUS) 3rd Feb1. Batavia Streamline12.002.92
1810. Shes A Harleequinn10.412.533AUS / Albany (AUS) 3rd Feb10. Shes A Harleequinn10.412.53
192. Bettor Look33.626.513AUS / Albany (AUS) 3rd Feb2. Bettor Look33.626.51
203. Dynamite Dolly34.666.463AUS / Albany (AUS) 3rd Feb3. Dynamite Dolly34.666.46
214. Twentynine Twelve7.802.493AUS / Albany (AUS) 3rd Feb4. Twentynine Twelve7.802.49
225. Keptain Courageous2.511.413AUS / Albany (AUS) 3rd Feb5. Keptain Courageous2.511.41
236. Wattabout Rioli7.402.503AUS / Albany (AUS) 3rd Feb6. Wattabout Rioli7.402.50
247. Alta Camino29.255.983AUS / Albany (AUS) 3rd Feb7. Alta Camino29.255.98
258. Highly Flammable19.514.243AUS / Albany (AUS) 3rd Feb8. Highly Flammable19.514.24
269. Lorinda Kate213.1016.993AUS / Albany (AUS) 3rd Feb9. Lorinda Kate213.1016.99
Back Win Lay Place
Cell Formulas
RangeFormula
D2:D26D2=IF(U2="","",U2)
E2:E26E2=IF(W2="","",W2)
F2:F26F2=IF(Y2="","",Y2)
Y2:Y26Y2=IF(ISNA(VLOOKUP(U2,$AA$2:$AC$1000,3,0)),"",VLOOKUP(U2,$AA$2:$AC$1000,3,0))
G3:G26G3=IF(R3="","",IF(R2=R3,G2,G2+1))
 
Upvote 0
Thanks guys for your help, Peter that second option worked perfectly.
 
Upvote 0
You're welcome. Thanks for the confirmation. :)
.. and for updating your version details. (y)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top