Forum Discussion
Formula red dotted lines around cell.
1. **Delimiter Issue:**
- In some regions, Excel uses a semicolon (`;`) as the delimiter instead of a comma (`,`). If your Excel version uses semicolons, ensure that your formulas use the correct delimiter.
2. **Function Names:**
- Make sure you are using the correct function names and that they are spelled correctly. Excel functions are case-insensitive, but if there's a typo, the formula might not work.
3. **Cell References:**
- Check that your cell references are accurate. If you are referencing a range of cells, ensure the range is correct, and there are no typos in the reference.
4. **Data Types:**
- Ensure that the data types in your formula are compatible. For example, if you are performing a mathematical operation, make sure that the cells contain numerical data.
5. **Error Checking:**
- Excel usually provides error messages if there's an issue with the formula. If you don't see an error message, you might want to check the error checking options.
- Go to the "Formulas" tab.
- Click on "Error Checking" and then choose "Error Checking Options."
- Make sure that the "Enable background error checking" and "Error checking rules" options are selected.
6. **Cell Formatting:**
- Verify that the cells involved in the formula are formatted correctly. Sometimes, formatting issues can affect calculations.
7. **Calculation Settings:**
- Check your Excel calculation settings. Excel might be set to manual calculation mode, preventing formulas from updating automatically. You can change this setting in the Formulas tab under Calculation Options.
If the issue persists, it might be helpful to provide more details about the specific formula or function you are using, along with some sample data. This will allow for a more targeted and accurate solution to the problem.
Thank you for the reply. I followed all the the steps still no success.
the formula is =IF(AD2>300,”Large”,IF(AD2>100,”Medium”,IF(AD2>0,”Small”)))
I tried =IF(AD2>300,”Large”;IF(AD2>100,”Medium”;IF(AD2>0,”Small”)))
I'm at my wits end I been stuck 2 weeks and no one can help me. I can skip it but I have a Practical with the next week content but I'm ahead on the studies.
Three observations:
1) The numbers in column AD are left aligned, so these are probably texts. Can't really see if you used a comma or a point as the decimal separator. Formatting a text as a number will not resolve the issue. You need to enter the numbers in the correct local format.
2) And if that local format expects a decimal point, then the separator to be used between the arguments in a formula needs to be a comma. If the decimal is marked with a comma, you need to use semi-colons in the formulas stead. The red dotted line indicated that you have used the wrong separator.
3) When I copy your first formula to an Excel sheet in the on-line version I get italic quotation marks and a NAME error. When, I replace them with regular quotation marks and correct the numbers (see 1 above), the function works. Not correcting the numbers will always return Large, so you really need to fix that first. My set-up expects comma as the list separator, so no problem there. No red dots around the cell.
- Thank you. The delimiter worked. but now i have a #NAME? I replaced all the quotation marks and the formula is working. . The red dotted line at least is gone and I know that it refers to the separator. error. ill try and sort it out. I'm still very much a noob but thank you for helping.
- Can you share a link to view the fle ?
