Forum Discussion

Ibrahim475's avatar
Ibrahim475
Copper Contributor
May 28, 2024

Error: Excel doe'snt recognise the formula and tells me to start it with "=" sign

I am establishing a blood electrolytes data analysis using Excel . I entered the following formula to classify the result of calcium but Excel would not accept it and tells me it is text and should ...
training
SnowMan55's avatar
SnowMan55
Bronze Contributor
May 29, 2024

Ibrahim475 

There are multiple problems here:

  1. Most of the right parentheses are misplaced. See the formula below for proper nested IF functions. (Here the formula is split across multiple lines within the Formula Bar, with exaggerated indentation, and spaces between some arguments; those changes make the formula easier to read—for example, it's easier to see which right parenthesis closes which IF function—but these adornments are certainly not required.)
  2. If the test for <2.2 is not satisfied (when it "fails", loosely speaking), you don't need the redundant test for =>2.2. Besides, the valid comparison operator there would be >=, not =>. And even if you did need that comparison, you would combine them as in IF( AND(H10>=2.2, H10<=2.55) …
  3. You likely mistyped a numeric value for the Moderate- and/or Crisis-level tests; look closely, and you will see that the upper bound for Moderate is larger than the lower bound for Crisis.
=IF(H10<2.2, "Low",
        IF(H10<=2.55, "",
                IF(H10<=2.61975, "Mild Hypercalcemia",
                        IF(H10<=3.46905, "Moderate Hypercalcemia",
                                IF(H10>3.46705, "HYPERCALCEMIC CRISES", "")
                        )
                )
        )
)


But really, the better design is probably not to put all of those values into a formula, if this formula will be repeated over and over. Instead, put those values into a specific range (or an Excel table) somewhere (likely on a separate worksheet), and use an Excel lookup function (such as VLOOKUP) to establish the condition. Now, that could be a problem in the future, when those numeric values need to be changed. You could at that point to replace the formulas with values on existing records, edit any new formulas (but with multiple occurrences, that's prone to errors), and use new formulas/reference values going forward. Or you could build the range/table data with "valid through" dates.  Anyway, it's something to plan for.

 

Resources