Mix IF and MID

[tinferns]

Hi,
Can I mix IF with MID command?

I am trying a formula which is not working the way it should. Can you please help me ?

Q16 = 1, 0, 3, 7, 2 (I will have numbers between 1-9 in random order) I want to count all numbers above 1 (I dont want SUM). In this case I want result to be 4. (By COUNTing 1, 3, 7, and 2)

I tried : =IF(MID(Q16,1,1)>1,1)+IF(MID(Q16,4,1)>1,1)+IF(MID(Q16,7,1)>1,1)+IF(MID(Q16,10,1)>1,1)+IF(MID(Q16,13,1)>1,1,0)

I am getting result 5. Wherein it is even counting for a Zero. I want to count only 1-9.

Can you help me

Thanks in advance.

Martin

 

[Nogslaw]

The MID function returns a String value, so it is reading your digits as text. You can convert to numerical values by using the VALUE function. Try:

=IF(VALUE(MID(Q16,1,1))>1,1)+IF(VALUE(MID(Q16,4,1))>1,1)+IF(VALUE(MID(Q16,7,1))>1,1)+IF(VALUE(MID(Q16,10,1))>1,1)+IF(VALUE(MID(Q16,13,1))>1,1,0)

Note, this formula as it is written will only work if you have consistent data formatted exactly the way you showed in your example ... digit comma space digit comma space ... etc. and containing exactly 5 digits.

 

[sanrv1f]

try this

Excel Workbook
	A	B
2	1, 0, 3, 7, 2	4
Sheet2

Excel 2003

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(MID(A2,(ROW(OFFSET($A$1,,,INT(LEN(A2)/3)+1))-1)*3+1,1)+0>=1))

 

[tinferns]

Thanks Nogslaw for that promt reply.,

The formula works fine if I have exactly 5 digits. Any way of working out if there are less than or more than 5 digits ?

I may have minimum 2 to a max of 7 digits.

Can it be sorted ?

Please advise.

Thanks in advance.

 

[Nogslaw]

The formula sanrv1f posted will work for varying lengths, although you'll have to have him explain it to you!!

 

[Aladin Akyurek]

Try...

=SUMPRODUCT((EVAL("{"&Q16&"}")>1)+0)

EVAL is available either from the free morefunc.xll add-in or as VBA code by FastExcel on this board.

 

[pgc01]

Hi Martin

Assuming like in your example that the string only has spaces, commas and digits, try in B1:

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"0",""))


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1, 0, 3, 7, 2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1, 4, 5, 0,4,0, 5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>

 

[Nogslaw]

You guys are awesome ... I think it is great how many different approaches have been taken to this post!

I'm sure there are dozens more that could be used as well.

 

[tinferns]

Thanks PCG,
I will stick to SUBSTITUTE.

Hey sanrv1f,
Your formula is good too.. 1 query.. what is A$1$ for ? Do I have to leave A1 blank ?

Please advise..

Thanks to all of you.

Cheers


Martin

 

[sanrv1f]

Hi Martin

Assuming like in your example that the string only has spaces, commas and digits, try in B1:

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"0",""))


<table style="border-color: rgb(204, 204, 204); border-width: 2px; background: rgb(255, 255, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; border-collapse: collapse; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="1"><tbody><tr><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;"> </th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; text-align: center;">A</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; text-align: center;" width="30">B</th><th style="border-color: rgb(136, 136, 136); border-width: 1px; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; text-align: center;" width="30">C</th></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; text-align: center;">1</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">1, 0, 3, 7, 2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">4</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; text-align: center;">2</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: left;">1, 4, 5, 0,4,0, 5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;">5</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td style="border-color: rgb(0, 0, 0); border-width: 1px; padding: 0.4em 0.5em 0.25em; background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; text-align: center;">3</td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td><td style="border-color: rgb(136, 136, 136); border-width: 1px; padding: 0.4em 0.5em 0.25em; text-align: right;"> </td></tr><tr><td colspan="4" style="background: rgb(153, 204, 255) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; padding-left: 1em;"> [Book1]Sheet2</td></tr></tbody></table>

PGC,

That classic ray:

Hey sanrv1f,
Your formula is good too.. 1 query.. what is A$1$ for ? Do I have to leave A1 blank ?

Please advise..

Martin,

Cell A1 plays the roll of an anchor in the OFFSET function, the content of the cell doesn't affect the formula, when you evaluate the formula step-by-step, you would realize that the OFFSET brings up the range A1:A5 if the cell contains 5 digits and the outer ROW function gives you an array of {1;2;3;4;5}, this could be used in the MID to get 1 char from the respective postions