MIN value without 0 in seperate cells?

[Bpuad]

I have a problem that I can't find the answer to.

I need to extract the lowest value (that is not 0) which is no problem but how do I do this when the cells are separated from each other e.g. a1 a5 a9 a13 a17?

/Per

 

[jraj1106]

Hello....
I hope this is what you wanted...

=MINA(A1,A5,A9,A13,A17)

 

[pgc01]

Hi
Welcome to the board

Try:

=MIN(IF(MOD(ROW(A1:A17),4)=1,IF(A1:A17<>0,A1:A17)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

 

[pgc01]

Hello....
I hope this is what you wanted...

=MINA(A1,A5,A9,A13,A17)

Hi jraj1106

You are not discarding the 0's, like Bpuad asked.

 

[jraj1106]

Sorry I didn't read it properly..

Thanks...

 

[Bpuad]

Hi
Welcome to the board

Try:

=MIN(IF(MOD(ROW(A1:A17),4)=1,IF(A1:A17<>0,A1:A17)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

Hi thank you, it's a great forum

but I only want some of the cells in the row to be included, not all? See the exaple below, I only want min value (exclude 0) for the cells marked with an x.

<TABLE style="WIDTH: 645pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=857><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2112" width=66><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2048" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2368" width=74><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816" width=88><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2048" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2368" width=74><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3552" width=111><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" height=19 width=72></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=66>A</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=77>B</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=64>C</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=74>D</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=81>E</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=88>F</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=64>G</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=74>H</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=86>I</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" width=111>J</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl71 height=19>1</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fcd5b4; WIDTH: 50pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl65 width=66>x</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fac090; WIDTH: 58pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl66 width=77></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fcd5b4; WIDTH: 56pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl65 width=74></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fac090; WIDTH: 61pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl66 width=81>x</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fcd5b4; WIDTH: 66pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl65 width=88></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl66 width=64></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fac090; WIDTH: 56pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl66 width=74></TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fcd5b4; WIDTH: 65pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl65 width=86>x</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: #fac090; WIDTH: 83pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl66 width=111></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl71 height=19>2</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl67>0</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl68 width=77>836400</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl68 width=64>1234</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl67>80100</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl69 width=81>192240</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl68 width=88>1153440</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl70 width=64>1324</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl70 width=74>81300</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl67>178209,6</TD><TD style="BORDER-BOTTOM: #e2e2e2; BORDER-LEFT: #e2e2e2; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #e2e2e2; BORDER-RIGHT: #e2e2e2" class=xl68 width=111>1153809,6</TD></TR></TBODY></TABLE>

 

[Robert Mika]

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1688 height=17 width=64>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1690 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1690 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1691 width=64>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1690 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1688 width=64>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 height=34 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64 align=right>836400</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64 align=right>1234</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>80100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>192240</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64 align=right>1153440</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>1324</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>81300</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64>178209,6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64>1153809,6

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 align=right>192240</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694> </TD></TR></TBODY></TABLE>

Array formula confirm CTRL+SHIFT+ENTER:
=MIN(IF($A$1:$J$1="x",IF($A$2:$J$2>0,$A$2:$J$2)))

 

[T. Valko]

Hi thank you, it's a great forum

but I only want some of the cells in the row to be included, not all? See the exaple below, I only want min value (exclude 0) for the cells marked with an x.

Try one of these array formulas**.

If you will never insert new columns before the data range:

=MIN(IF(MOD(COLUMN(A2:J2),4)=1,IF(A2:J2<>0,A2:J2)))

If you might insert new columns before the data range:

=MIN(IF(MOD(COLUMN(A2:J2)-COLUMN(A2),4)=0,IF(A2:J2<>0,A2:J2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Bpuad]

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1688 height=17 width=64>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1690 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1690 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1691 width=64>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1690 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fcd5b4; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1688 width=64>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1689 width=64></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 height=34 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64 align=right>836400</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64 align=right>1234</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>80100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>192240</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64 align=right>1153440</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>1324</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64 align=right>81300</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1692 width=64>178209,6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1693 width=64>1153809,6


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 height=17></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 height=17></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 height=17></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694 align=right>192240</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl1694></TD></TR></TBODY></TABLE>

Array formula confirm CTRL+SHIFT+ENTER:
=MIN(IF($A$1:$J$1="x",IF($A$2:$J$2>0,$A$2:$J$2)))

Thanks, but I forgot to mention that there are no X in the real sheet just headlines, diffrent ones.

Sorry for that.

 

[Bpuad]

Try one of these array formulas**.

If you will never insert new columns before the data range:

=MIN(IF(MOD(COLUMN(A2:J2),4)=1,IF(A2:J2<>0,A2:J2)))

If you might insert new columns before the data range:

=MIN(IF(MOD(COLUMN(A2:J2)-COLUMN(A2),4)=0,IF(A2:J2<>0,A2:J2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks, but it still includes all the cells not just A2, E2 and I2? Or am I doing something wrong?