SUM of multiple MIN ranges while ignoring cells with Text "N/A"(without quotes)

[b17g]

Hello,

So this is my current forumla:

=SUM(MIN(B2:N2),(B3:N3),(B4:N4),(B5:N5),(B6:N6),(B7:N7),(B8:N8),(B9:N9),(B10:N10),(B11:N11),(B12:N12))

I'm basically comparing prices for something, and certain companies don't make certain products; So I have N/A's in those boxes. How can I adjust my formula so that it ignores cells with "N/A"(without quotes)?

Thank you for your help.

 

[markmzz]

Yeah it worked, but now it's not for some reason -_-. Ugh. It's assuming E1+300 is cell E1 and it's bugging out. It screwed up when I cut and pasted it from one cell to another that I had formatted with color. Here's the current formula:

=MIN(IF(ISERROR(B2:N2),E1+300,(B2:N2))+MIN(IF(ISERROR(B3:N3),E1+300,(B3:N3))+MIN(IF(ISERROR(B4:N4),E1+300,(B4:N4))+MIN(IF(ISERROR(B5:N5),E1+300,(B5:N5))+MIN(IF(ISERROR(B6:N6),E1+300,(B6:N6))+MIN(IF(ISERROR(B7:N7),E1+300,(B7:N7))+MIN(IF(ISERROR(B8:N8),E1+300,(B8:N8))+MIN(IF(ISERROR(B9:N9),E1+300,(B9:N9))+MIN(IF(ISERROR(B10:N10),E1+300,(B10:N10))+MIN(IF(ISERROR(B11:N11),E1+300,(B11:N11))+MIN(IF(ISERROR(B12:N12),E1+300,(B12,N12))

Hmmmmm! Why does it look uniform from B3:N3 and then it screws up at B9 and B10. Hopefully someone can find my error. It's having a error with The min is error for B8:N8

Is 1E+300 and not E1+300.

=MIN(IF(ISERROR(B2:N2),1E+300,B2:N2))

Did you try my suggestion in my last post (helper column)?

Markmzz

 

[b17g]

No unfortunately I have yet to try that, I don't quite understand how it works.

I fixed the 1e/e1 problem. I'm just having a problem half way down where it says I have a problem with:

+MIN(IF(----->ISERROR<----(B8:N8),1E+300,(B8:N8)). I don't understand why it has an error with that one over all the preceding ones.

 

[markmzz]

IMPORTANT: IN THE FORMULA IS 1E+300 AND NOT E1+300.

Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Helper Column</td><td style="text-align: center;background-color: #FFFF00;;">Sum</td><td style="text-align: center;background-color: #FFFF00;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">4</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">16</td><td style="text-align: center;background-color: #FFFF00;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">8</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;">8</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">6</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">6</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">6</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet10</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q2</th><td style="text-align:left">=SUM(<font color="Blue">P2:P12</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B2:N2</font>),1E+300,B2:N2</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B2:N2</font>),1E+300,B2:N2</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B3:N3</font>),1E+300,B3:N3</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B4:N4</font>),1E+300,B4:N4</font>)</font>)+
MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B5:N5</font>),1E+300,B5:N5</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B6:N6</font>),1E+300,B6:N6</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B7:N7</font>),1E+300,B7:N7</font>)</font>)+
MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B8:N8</font>),1E+300,B8:N8</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B9:N9</font>),1E+300,B9:N9</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B10:N10</font>),1E+300,B10:N10</font>)</font>)+
MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B11:N11</font>),1E+300,B11:N11</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISERROR(<font color="Green">B12:N12</font>),1E+300,B12:N12</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[b17g]

Oh I see. Take it one by one and then do a sum of all of them rather than tryin to do it all in one.... But that's not pretty

So I'm taking your formula again and using it for just one line and no it's giving me 1E+300 in the box. I'm thinking that the syntax makes it so that if it finds an error it gives you "1E+300?"

 

[markmzz]

Try this new array formula for each min:

=IF(COUNT(B2:N2)=0,0,MIN(IF(ISERROR(B2:N2),E1+300,B2:N2)))

Markmzz

 

[markmzz]

Another array formula for each min:

=MIN(IF(COUNT(B2:N2),IF(ISERROR(B2:N2),E1+300,B2:N2)))

Markmzz

 

[markmzz]

Try this new array formula for each min:

=IF(COUNT(B2:N2)=0,0,MIN(IF(ISERROR(B2:N2),E1+300,B2:N2)))

Markmzz

Another array formula for each min:

=MIN(IF(COUNT(B2:N2),IF(ISERROR(B2:N2),E1+300,B2:N2)))

Markmzz

b17g,

The correct in the two formulas above is 1E+300 and not E1+300.

Markmzz

 

[barry houdini]

In general you can get the minimum value in a range ignoring errors with an array formula like this

=MIN(IF(ISNUMBER(range),range))

You shouldn't really need "Bignum"......

......but aren't those "N/A"s text values rather than errors? If so then you don't need any "error handling", MIN function will ignore text values anyway.......so this formula should suffice along the lines of your original

=SUM(MIN(B2:N2),MIN(B3:N3),MIN(B4:N4),MIN(B5:N5),MIN(B6:N6),MIN(B7:N7), MIN(B8:N8),MIN(B9:N9),MIN(B10:N10),MIN(B11:N11),MIN(B12:N12))

...or you can shorten that with this version

=SUMPRODUCT(SUBTOTAL(5,OFFSET(B2:N12,ROW(B2:N12)-ROW(B2),0,1)))

 

[markmzz]

In general you can get the minimum value in a range ignoring errors with an array formula like this

=MIN(IF(ISNUMBER(range),range))

You shouldn't really need "Bignum"......

......but aren't those "N/A"s text values rather than errors? If so then you don't need any "error handling", MIN function will ignore text values anyway.......so this formula should suffice along the lines of your original

=SUM(MIN(B2:N2),MIN(B3:N3),MIN(B4:N4),MIN(B5:N5),MIN(B6:N6),MIN(B7:N7), MIN(B8:N8),MIN(B9:N9),MIN(B10:N10),MIN(B11:N11),MIN(B12:N12))

...or you can shorten that with this version

=SUMPRODUCT(SUBTOTAL(5,OFFSET(B2:N12,ROW(B2:N12)-ROW(B2),0,1)))

Thanks Barry Houdini,

I completely forgot the ISNUMBER function.

Then, with the Barry Houdini's suggestion you can try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">Sum</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Helper Column</td><td style="text-align: center;background-color: #FFFF00;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">4</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">8</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">7</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R2</th><td style="text-align:left">=SUM(<font color="Blue">Q2:Q12</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B2:N2</font>),B2:N2</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B3:N3</font>),B3:N3</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B4:N4</font>),B4:N4</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B5:N5</font>),B5:N5</font>)</font>)+
MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B6:N6</font>),B6:N6</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B7:N7</font>),B7:N7</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B8:N8</font>),B8:N8</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B9:N9</font>),B9:N9</font>)</font>)+
MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B10:N10</font>),B10:N10</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B11:N11</font>),B11:N11</font>)</font>)+MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B12:N12</font>),B12:N12</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Q2</th><td style="text-align:left">{=MIN(<font color="Blue">IF(<font color="Red">ISNUMBER(<font color="Green">B2:N2</font>),B2:N2</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz