Formula red dotted lines around cell.
I need assistance asap. I'm doing studies and the formulas I need to work with do not give an error they just hang there. I'm using online Excel. I can copy the formula from the studies or type i...
Forum Discussion
It sounds like you are facing issues with Excel formulas, and it's crucial to troubleshoot and identify the problem. Here are some common reasons why formulas might not be working as expected:
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.
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.
mr_T665 Please have a look at the attached file and compare the quote marks with yours.
- I was too Hasty with my Reply as now the formulas is there its working after i used the fill handle then its no filtering the amounts and everything is calculated as medium.
If I look t a formula in a column that i can see is incorrect and click on different cell block it turns to False.
- Can you share a link to view the fle ?
