Excel Formula Question

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
814
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone,

I have a range of cells A1:F1 with the following values:
A1 = 667
B1 = 577
C1 = 568
D1 = 467
E1 = 557
F1 = 566

There are 4 values between 500 and 599. 577, 568, 557, 566
Cell G1 I have the value 500 which indicates any values of 500 through 599
Cell range I1. If countif values in cell range A1:F1 are between 500 and 599 enter the 1st value in cell I1 starting with the smallest of the 500 through 599 values from A1 through F1.
So cell range I1 through L1:
I1 = 557
J1 = 566
K1 = 568
L1 = 577

Thank you in advance!!
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,333
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1667577568467557566500557566568577  
Data
Cell Formulas
RangeFormula
H1:M1H1=IFERROR(AGGREGATE(15,6,$A$1:$F$1/($A$1:$F$1>=$G$1)/($A$1:$F$1<=$G$1+99),COLUMNS($H1:H1)),"")
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
814
Office Version
  1. 2010
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1667577568467557566500557566568577  
Data
Cell Formulas
RangeFormula
H1:M1H1=IFERROR(AGGREGATE(15,6,$A$1:$F$1/($A$1:$F$1>=$G$1)/($A$1:$F$1<=$G$1+99),COLUMNS($H1:H1)),"")
Excellent work Fluff. Works great. Exactly what I was looking for.
Stay Safe!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,333
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

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
Top