=small function - skipping cells with formulae but no value

G

goliath1

New Member
Joined
Nov 8, 2017
Messages
5
Hi i searched thru the forum but could not find any reference to my specific question.

I want the 5 lowest values in a row. The cells in the row may or may not have values. When i use the =SMALL function it thinks my blank cell is one of 5 smallest.
=SUM(SMALL(G4:U4,{1,2,3,4,5})) is the formula in the cell below with value=153. It is summing the 5 lowest values,

It is summing the 5 cells with the "X" above them. How do i skip the blank cell in YELLOW? BTW, each of these colored cells contain a vlookup formula.
G4---------------------------------------------------------------------------------U4
x x x x x[TABLE="width: 781"]
<tbody>[TR]
[TD="class: xl69, width: 50"]153[/TD]
[TD="class: xl69, width: 50"]14[/TD]
[TD="class: xl70, width: 50"]4[/TD]
[TD="class: xl71, width: 43"]-5.4[/TD]
[TD="class: xl68, width: 57"]-5[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 33, align: center"] [/TD]
[TD="class: xl70, width: 33, align: center"]38[/TD]
[TD="class: xl70, width: 33, align: center"]45[/TD]
[TD="class: xl70, width: 33, align: center"]49[/TD]
[TD="class: xl70, width: 33, align: center"]51[/TD]
[TD="class: xl70, width: 33, align: center"]43[/TD]
[TD="class: xl70, width: 33, align: center"]41[/TD]
[TD="class: xl70, width: 33, align: center"]52[/TD]
[TD="class: xl70, width: 33, align: center"]39[/TD]
[TD="class: xl70, width: 39, align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]

I want it to pick up these 5 values, 40, 40, 38, 39, 36
x x x x x
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 781"]
<tbody>[TR]
[TD="class: xl69, width: 50"]153[/TD]
[TD="class: xl69, width: 50"]14[/TD]
[TD="class: xl70, width: 50"]4[/TD]
[TD="class: xl71, width: 43"]-5.4[/TD]
[TD="class: xl68, width: 57"]-5[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 39"]40[/TD]
[TD="class: xl70, width: 33"] [/TD]
[TD="class: xl70, width: 33"]38[/TD]
[TD="class: xl70, width: 33"]45[/TD]
[TD="class: xl70, width: 33"]49[/TD]
[TD="class: xl70, width: 33"]51[/TD]
[TD="class: xl70, width: 33"]43[/TD]
[TD="class: xl70, width: 33"]41[/TD]
[TD="class: xl70, width: 33"]52[/TD]
[TD="class: xl70, width: 33"]39[/TD]
[TD="class: xl70, width: 39"]36

[/TD]
[/TR]
</tbody>[/TABLE]
</body>[TABLE="width: 588"]
<tbody>[TR]
[TD="class: xl69, width: 50"]153[/TD]
[TD="class: xl69, width: 50"]14[/TD]
[TD="class: xl70, width: 50"]4[/TD]
[TD="class: xl71, width: 43"]-5.4[/TD]
[TD="class: xl68, width: 57"]-5[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 39, align: center"]40[/TD]
[TD="class: xl70, width: 33, align: center"] [/TD]
[TD="class: xl70, width: 33, align: center"]38[/TD]
[TD="class: xl70, width: 33, align: center"]45[/TD]
[TD="class: xl70, width: 33, align: center"]49[/TD]
[TD="class: xl70, width: 33, align: center"]51[/TD]
[TD="class: xl70, width: 33, align: center"]43[/TD]
[TD="class: xl70, width: 33, align: center"]41[/TD]
[TD="class: xl70, width: 33, align: center"]52[/TD]
[TD="class: xl70, width: 33, align: center"]39[/TD]
[TD="class: xl70, width: 39, align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]

any ideas?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Hi i searched thru the forum but could not find any reference to my specific question.

I want the 5 lowest values in a row. The cells in the row may or may not have values. When i use the =SMALL function it thinks my blank cell is one of 5 smallest.
=SUM(SMALL(G4:U4,{1,2,3,4,5})) is the formula in the cell below with value=153. It is summing the 5 lowest values,

It is summing the 5 cells with the "X" above them. How do i skip the blank cell? BTW, each of these cells G4:U4 contain a vlookup formula.
-----------------------------------G4----------------------------------------------------------------------------------U4
--------------------------------------------------------------X---X---X------------------------------------------X-----X

[TABLE="class: cms_table, width: 781"]
<tbody>[TR]
[TD="class: cms_table_xl69, width: 50"]153[/TD]
[TD="class: cms_table_xl69, width: 50"]14[/TD]
[TD="class: cms_table_xl70, width: 50"]4[/TD]
[TD="class: cms_table_xl71, width: 43"]-5.4[/TD]
[TD="class: cms_table_xl68, width: 57"]-5[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]40[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"][/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]38[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]45[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]49[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]51[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]43[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]41[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]52[/TD]
[TD="class: cms_table_xl70, width: 33, align: center"]39[/TD]
[TD="class: cms_table_xl70, width: 39, align: center"]36[/TD]
[/TR]
</tbody>[/TABLE]


I want it to pick up these 5 values, 40, 40, 38, 39, 36 but it appears to be selecting 40,38,39,36 and blank.

[TABLE="class: cms_table, width: 781"]
<tbody>[TR]
[TD="class: cms_table_xl69, width: 50"]153[/TD]
[TD="class: cms_table_xl69, width: 50"]14[/TD]
[TD="class: cms_table_xl70, width: 50"]4[/TD]
[TD="class: cms_table_xl71, width: 43"]-5.4[/TD]
[TD="class: cms_table_xl68, width: 57"]-5[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 39"]40[/TD]
[TD="class: cms_table_xl70, width: 33"][/TD]
[TD="class: cms_table_xl70, width: 33"]38[/TD]
[TD="class: cms_table_xl70, width: 33"]45[/TD]
[TD="class: cms_table_xl70, width: 33"]49[/TD]
[TD="class: cms_table_xl70, width: 33"]51[/TD]
[TD="class: cms_table_xl70, width: 33"]43[/TD]
[TD="class: cms_table_xl70, width: 33"]41[/TD]
[TD="class: cms_table_xl70, width: 33"]52[/TD]
[TD="class: cms_table_xl70, width: 33"]39[/TD]
[TD="class: cms_table_xl70, width: 39"]36
[/TD]
[/TR]
</tbody>[/TABLE]





any ideas?
 
Upvote 0
Try this formula entered as an Array

=SUM(SMALL(IF(G3:U3="X",G4:U4),{1,2,3,4,5}))

This is an array formula that requires special keystroke to enter it.
Instead of pressing ENTER after typing the formula, you must press CTRL + SHIFT + ENTER
You will know the formula is correctly entered when it is enclosed in {brackets}
 
Upvote 0
I think SMALL will ignore blanks and text so if it's picking the "blank" cell I think that cell contains a zero. You can use this formula to sum the smallest 5 without zero

=SUM(AGGREGATE(15,6,G4:U4/(G4:U4<>0),{1,2,3,4,5}))
 
Upvote 0
THE "x" in the post was only to point out which cells i needed to pick out.

I just want the function =SMALL to ignore cells that are not numeric.
 
Upvote 0
goliath1 said:
THE "x" in the post was only to point out which cells i needed to pick out.

I just want the function =SMALL to ignore cells that are not numeric.
Click to expand...
My mistake, I misread your post thinking you wanted the ones that has an x in a row above..

As Barry said, small DOES ignore blanks and Non Numeric Values.
I would guess that your blanks are actually real 0's. But you have 0s hidden either by some formatting, or in Advanced options you can uncheck "Show a zero in cells with zero value"
 
Upvote 0
As per my reply, SMALL function will only pick numbers, so your blank is probably really a zero (formatted to display as a blank?) so try the formula I suggested in my last post......
 
Upvote 0
barry houdini said:
I think SMALL will ignore blanks and text so if it's picking the "blank" cell I think that cell contains a zero. You can use this formula to sum the smallest 5 without zero

=SUM(AGGREGATE(15,6,G4:U4/(G4:U4<>0),{1,2,3,4,5}))
Click to expand...


WOOHOO. i have been working on this for hours. :-) works like a top. thx again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
304
Peter_SSs
Peter_SSs
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements
J
  • Solved
LET/VSTACK/FILTER - additional functionality question
2
Replies
14
Views
759
Jedi Master
J
J
  • Solved
#N/A Error
2
Replies
14
Views
359
Jedi Master
J
Z
  • Question Question
Xlookup with multiple conditions
Replies
5
Views
259
Cubist
Cubist

Forum statistics

Threads
1,224,823
Messages
6,181,178
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