Ranking with spaces.

  • Thread starter Thread starter Howard Brazee
  • Start date Start date
H

Howard Brazee

I have this Excel function

=IF(F4>0,RANK(F4,$F$4:$F$102,1)," ")

Except I have a bunch of rows which are blanked out. What I want is to rank
only the non-blank rows.

Actually, what I *really* need to find the lowest two non-blank numbers in
column F: to use in a calculation.

Any advice?
 
Hi Howard,
To find the smallest two values

=SMALL($F$4:$F$102,1) and =SMALL($F$4:$F$102,2)

will do. SMALL ignores spaces and text
 
Lowest non-blank number:

=SMALL(IF($F$4:$F$102<>"",$F$4:$F$102),1)

Second lowest non-blank number:

=SMALL(IF($F$4:$F$102<>"",$F$4:$F$102),2)

If you want to average the two lowest numbers...

=AVERAGE(SMALL(IF($F$4:$F$102<>"",$F$4:$F$102),{1,2}))

These formulas need to be entered using CONTROL+SHIFT+ENTER.

Hope this helps!
 
Lowest non-blank number:

=SMALL(IF($F$4:$F$102<>"",$F$4:$F$102),1)

Second lowest non-blank number:

=SMALL(IF($F$4:$F$102<>"",$F$4:$F$102),2)

If you want to average the two lowest numbers...

=AVERAGE(SMALL(IF($F$4:$F$102<>"",$F$4:$F$102),{1,2}))

These formulas need to be entered using CONTROL+SHIFT+ENTER.

Hope this helps!
Click to expand...

Thanks that will.

I have changed the sheet some though. I created a column E with this formula:
=RANK(D4,$D$4:$D$102,1)

What I really want is another formula containing the values of A (or D, they
will be the same), for when E = 1 or 2. I can put these two values in cells
somewhere that I can reference them in my formula.

I think I can do this by playing around with the first formula you gave me.

(I set up D to contain 1000 when the data used for A does not meet my criteria.)
 
I'm not clear as to what you're looking for. Can you post a small
sample of your spreadsheet (plain text) and your expected results?
 
I'm not clear as to what you're looking for. Can you post a small
sample of your spreadsheet (plain text) and your expected results?
Click to expand...

I have the problem solved now, thanks to your input. Now to the next problem -
looking up a value in a table using this result.

(this is a two dimensional table with ranges for for each row and each column).
Boy I am leaving a lot of intermediate values around in cells.
 
Howard Brazee said:
I have the problem solved now, thanks to your input.
Click to expand...

Note, as Bernard has pointed out, the function SMALL ignores blanks so
my formulas should be changed to...

=SMALL($F$4:$F$102,1)

and

=SMALL($F$4:$F$102,2)

....entered using just ENTER.
Now to the next problem -
looking up a value in a table using this result.

(this is a two dimensional table with ranges for for each row and each
column).
Boy I am leaving a lot of intermediate values around in cells.
Click to expand...

If you need help with this, post a small sample of your table (plain
text) along with your expected results.
 
Back
Top