Count if there is a number and answer is smallest value
I have 20 cells that I'm wanting to pull the lowest value out of each row. The problem is that there are cells without a value so I need to exclude any blank cells or zeros.
So i need the lowest value of a series of cells but only countif there is a value greater than zero...i'm hoping for an easy answer!
Hi
Suppose that your data in a1:a20 range.
=SMALL(IF(ISBLANK(A1:A20),"",A1:A20),1) Ctrl+Shift+Enter
Appreciate the help? CLICK *
Hi,
Maybe, assuming your data is in A1:J1:
=AGGREGATE(15,6,1/(A1:J1>0)*A1:J1,1)
Copy down as required.
Regards
Like this...
=SMALL(A1:T1,COUNTIF(A1:T1,0)+1)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
One issue, the cells are not all together. Sorry for not including this in the original post:
W8,Z8,AC8,AF8,AI8,AL8,AO8,AR8,AU8,AX8,BA8,BD8,BG8,BJ8,BM8,BP8,BS8,BV8,BY8,CB8
What's in the cells between the cells of interest?
If there are no numbers then you can still use my suggestion.
There is different data between cells. I have it divided by locations.
If it helps, the original data is coming from separate table that would put the data all together in a column but then I would need to figure out how offset the formula to skip down 20 when I fill the other rows.
Maybe be safe with an array formula**:
=MIN(IF(MOD(COLUMN(W8:CB8)-MIN(COLUMN(W8:CB8)),3)=0,IF(W8:CB8>0,W8:CB8)))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
In that case I would use XOR LX's suggestion in post #8.
Thank you all for your help, it looks like it is working.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks