Need formula to recognize text and numbers...

[foolycoolycurry]

I am working on a spread sheet for a project I am leading and was asked to do the following: scan the values in cells B3, D3, F3 and for each one that contains a value >= 0.3 display a "+" in cell J3. So the possible displays are +, ++, +++. This part of the request I figured by doing a series of nested "IF" statements and adding in the COUNTIF function. (note, they also wanted output of nothing (ie " "), No Data (ND) or "X" for one missing column. This request was fairly easy to fo with more nested IF(AND/Or) type statements. Also, A3 is a sample number, if A3 is blank, no data processing is done.)

Here is the code: =IF(OR(A3=""),"",IF(OR(B3="",D3="",F3=""),"ND",IF(OR(B3="x",D3="x",F3="x"),"x",IF(COUNTIF(B3:F3,">=0.3")=0,"-",IF(COUNTIF(B3:F3,">=0.3")=1,"+",IF(COUNTIF(B3:F3,">=0.3")=2,"++",IF(COUNTIF(B3:F3,">=0.3")=3,"+++","Error")))))))

However after the original spread sheet was done they added in one additional request. They would like cell J3 to not only recognize numeric data but also text. Specifically they say that sometimes they don't have numbers to put into B3 or D3. In these cases they use a "+" or "-". They would like the formula to still display as before but to incorporate +/- characters in the determination of +,++, +++.

There is a very long drawn out way of doing this, however Excel says there can only be so many functions in a cell. Does anyone know how to make a more compact formula that can do what I need?

 

[PRyankid]

You might be able to accomplish this by writing a function in Visual Basic and then just passing it cells B3, D3, and F3. Then you can just output a string to the cell.

 

[foolycoolycurry]

I had thought VB might be a solution however I don't know VB at this point. I was hoping there was a clever way of using the stock functions to accomplish this.

Thanks for the advice. If no one else can think of any thing it looks like I'll be studying VB soon

 

[NBVC]

Your alternatives are not quite clear.

But maybe you can alter the If(Countif()) sections to include an OR()?

e.g. ...If(Or(B3="-",D3="-",COUNTIF(B3:F3,">=0.3")=0),"-",...