SMALL problem is a big issue.

[GAJITCS]

Cells D4:I8 as listed.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text1[/TD]
[TD]Text2[/TD]
[TD]Text3[/TD]
[TD]Text4[/TD]
[TD]Text5[/TD]
[TD]Lowest[/TD]
[/TR]
[TR]
[TD]1.12[/TD]
[TD]2.23[/TD]
[TD]2.21[/TD]
[TD]1.22[/TD]
[TD]3.21[/TD]
[TD]1.22[/TD]
[/TR]
[TR]
[TD]1.29[/TD]
[TD]2.12[/TD]
[TD]1.89[/TD]
[TD]0.00[/TD]
[TD]2.01[/TD]
[TD]2.01[/TD]
[/TR]
[TR]
[TD]1.17[/TD]
[TD]0.00[/TD]
[TD]2.17[/TD]
[TD]2.88[/TD]
[TD]2.90[/TD]
[TD]1.34[/TD]
[/TR]
</tbody>[/TABLE]









Cells D4:H4 are a named range of EX_C

Cells I6 contains the following formula:
{=SMALL(IF(EX_C<>"X",D7:H7,""),COUNTIF(D7:H7,"<=0")+1)}

The idea is that where cells D4:H4 are empty, this formula returns the lowest non zero value in the cells in the same row from columns D:H. It works, though it does give a #NUM error if all cells have a zero value, which is most unlikely.

Where D4:H4 might contain an X, this denotes that the cells in that column should be excluded from the workings of the formula, hence the table above does not show any of the values from columns D or E in the result of the formula in column I.

This works as long as at least two of the columns from D through H are not exlcuded with an X. If only one column is not excluded, the formula displays #NUM ! where it should show the last remaining value from the unchecked column.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text1[/TD]
[TD]Text2[/TD]
[TD]Text3[/TD]
[TD]Text4[/TD]
[TD]Text5[/TD]
[TD]Lowest[/TD]
[/TR]
[TR]
[TD]1.12[/TD]
[TD]2.23[/TD]
[TD]2.21[/TD]
[TD]1.22[/TD]
[TD]3.21[/TD]
[TD]3.21[/TD]
[/TR]
[TR]
[TD]1.29[/TD]
[TD]2.12[/TD]
[TD]1.89[/TD]
[TD]0.00[/TD]
[TD]2.01[/TD]
[TD]2.01[/TD]
[/TR]
[TR]
[TD]1.17[/TD]
[TD]0.00[/TD]
[TD]2.17[/TD]
[TD]2.88[/TD]
[TD]2.90[/TD]
[TD]2.90[/TD]
[/TR]
</tbody>[/TABLE]









If there anything I can do within the formula to correct this or do i need to inject another column of trash data and hide it (yuk).

Many thanks.

 

[AhoyNC]

Try:

Excel Workbook
	D	E	F	G	H	I
4	X	x	X	x
5	Text1	Text2	Text3	Text4	Text5	Lowest
6	1.12	2.23	2.21	1.22	3.21	3.21
7	1.29	2.12	1.89	0	2.01	2.01
8	1.17	0	2.17	2.88	2.9	2.9
Sheet

 

[GAJITCS]

Try:

	D	E	F	G	H	I

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]x[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]Text1[/TD]
[TD="align: center"]Text2[/TD]
[TD="align: center"]Text3[/TD]
[TD="align: center"]Text4[/TD]
[TD="align: center"]Text5[/TD]
[TD="align: center"]Lowest[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]1.12[/TD]
[TD="align: center"]2.23[/TD]
[TD="align: center"]2.21[/TD]
[TD="align: center"]1.22[/TD]
[TD="align: center"]3.21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]3.21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]1.29[/TD]
[TD="align: center"]2.12[/TD]
[TD="align: center"]1.89[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2.01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2.01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]1.17[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2.17[/TD]
[TD="align: center"]2.88[/TD]
[TD="align: center"]2.9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2.9[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula I6 {=SMALL(IF($D$4:$H$4<>"x",IF($D6:$H6<>0,$D6:$H6)),1)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you AhoyNC.

This is a step closer. It cures the problem unless the unchecked column has a cell value of Zero. So if G4 was empty and H4 had the X, I7 will read #NUM !

Can I trap this with an ISError check and return a "" result in a more elegant way than:

=IF(ISERROR(SMALL(IF(Ex_C<>"X",IF(D6:H6<>0,D6:H6)),1)),"",SMALL(IF(Ex_C<>"X",IF(D6:H6<>0,D6:H6)),1))

 

[AhoyNC]

If you have later ver. of Excel (can't remember if it was 2007 or 2010 when IFERROR was added) you could use.

[TABLE="width: 483"]
<colgroup><col width="483"></colgroup><tbody>[TR]
   [TD="width: 483"]IFERROR(SMALL(IF($D$4:$H$4<>"x",IF($D6:$H6<>0,$D6:$H6)),1),"")[/TD]
 [/TR]
</tbody>[/TABLE]

 

[GAJITCS]

If you have later ver. of Excel (can't remember if it was 2007 or 2010 when IFERROR was added) you could use.

[TABLE="width: 483"]
<tbody>[TR]
[TD="width: 483"]IFERROR(SMALL(IF($D$4:$H$4<>"x",IF($D6:$H6<>0,$D6:$H6)),1),"")[/TD]
[/TR]
</tbody>[/TABLE]

Spot on.

Many thanks.