how to get the 7 lowest values in a row

[emmanuellej]

HI! im using microsoft excel 2004 for mac,
im trying to get the excel to tell me the 7 lowest values in a row from my excel file
for example, if my sheet was:
7
39
5
4
7
6
3
8
3
958
45
4
70
1

id want it to return:
5
4
7
6
3
8
3

Thank you so much!

 

[Aladin Akyurek]

HI! im using microsoft excel 2004 for mac,
im trying to get the excel to tell me the 7 lowest values in a row from my excel file
for example, if my sheet was:
7
39
5
4
7
6
3
8
3
958
45
4
70
1

id want it to return:
5
4
7
6
3
8
3

Thank you so much!

Let A2:A15 house the sample.

D1: 7

D2: Lowest Values

D3, just enter and copy down:

=IF(ROWS($D$3:D3)<=$D$1,SMALL($A$2:$A$15,ROWS($D$3:D3)),"")

 

[emmanuellej]

my values are from F2:F367
when i enter =IF(ROWS($D$3:D3)<=$D$1,SMALL($F$2:$F$367,ROWS($D$3:D3))) excel just says FALSE.. was there something else i was supposed to change in the equation you sent me?
thanks again

 

[Aladin Akyurek]

my values are from F2:F367
when i enter =IF(ROWS($D$3:D3)<=$D$1,SMALL($F$2:$F$367,ROWS($D$3:D3))) excel just says FALSE.. was there something else i was supposed to change in the equation you sent me?
thanks again

Enter 7 in D1.

Enter the following formula in D3 and copy down:

=IF(ROWS($D$3:D3)<=$D$1,SMALL($F$2:$F$367,ROWS($D$3:D3)),"")

 

[jasonb75]

Not sure if this is what you're looking for but I interepreted the problem in a different way to Aladin.


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">7</TD><TD style="TEXT-ALIGN: right">71</TD><TD> </TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">39</TD><TD style="TEXT-ALIGN: right">72</TD><TD> </TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">5</TD><TD style="TEXT-ALIGN: right">36</TD><TD> </TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: right">989</TD><TD> </TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">7</TD><TD style="TEXT-ALIGN: right">1030</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">6</TD><TD style="TEXT-ALIGN: right">1027</TD><TD> </TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right">1091</TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">8</TD><TD style="TEXT-ALIGN: right">1089</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">958</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">45</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">70</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2:G15</TD><TD>=IF(COUNT(F2:F8)=7,SUM(F2:F8),"")</TD></TR><TR><TD>I2:I8</TD><TD>=INDEX($F$2:$F$15,MATCH(MIN($G$2:$G$15),$G$2:$G$15,0)+(ROWS($1:1)-1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[emmanuellej]

Hi, Sorry im not quite sure what did you did which the equation, but im not quite sure you understand what im trying to do. I have a file with the flow rate for each day for an entire year. Im trying to get excel to tell me what week had the lowest flow rates, that is I want excel to tell me 7 lowest values in a row.. is that more clear?
thanks again
emmanuelle

 

[jasonb75]

my values are from F2:F367
thanks again

In G2 enter

=IF(COUNT(F2:F8)=7,SUM(F2:F8),"")

and autofill that down to G367.

To get the lowest 7, enter (where you want the first result)

=INDEX($F$2:$F$367,MATCH(MIN($G$2:$G$367),$G$2:$G$367,0)+(ROWS($1:1)-1))

and fill down 7 rows (including the one where you first enter the formula).

 

[emmanuellej]

it worked! thank you so so much jasonb75!