How to make ISFORMULA conditional format exclude 'manual' formulas

[HMSTPI]

I have a conditional format in Excel 2016 set to shade cells that are formulas using the ISFORMULA() function. However, it also shades cells that include two manually input values added --- i.e. =1000+2000. That cell is still a value that will need to be input and, thus, NOT shaded.

Any suggestions?

Thanks!

 

[HolyExcel]

wrap it in an if statement where something like =if(isnumber(search("~+",cell)),FALSE,isformula(cell),TRUE,FALSE)

 

[thisoldman]

If your formulas that don't contain functions have no parentheses, this might work:

=LEN(FORMULATEXT(A2))<>LEN(SUBSTITUTE(FORMULATEXT(A2),"(",""))

 

[HMSTPI]

Those didn't work because the real formulas can have either/both parenthesis and +. So I kept trying and got this to work:

=ISERR(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(Data[@CY]),"=",""),"+",""),"-","")))

Since all of the 'manual' formulas are simple add/subtract, removing the = + - characters will leave only digits. The real formulas will have a column letter left in the text causing the formula to evaluate as TRUE and, thereby, shading the cell.

 

[HMSTPI]

OH -- have to substitute the "." for "" also

=ISERR(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(Cell),".",""),"=",""),"+",""),"-","")))