error in value

Question

when i am using subtraction by cell referrencing, it shows an error. please help

Answer 1

Hi @PRINCE THAKUR, 

Thanks for sharing the screenshot. 

Based on what I can see, the subtraction formula itself appears conceptually correct. However, this type of error usually occurs due to cell formatting or invalid cell contents, rather than the subtraction logic itself. I recommend checking the following: 

1. Cell format  Please confirm that all referenced cells are formatted as Numbers (or Currency), not Text. If any input cell is stored as text, Excel will return an error when performing arithmetic operations. 
2. Hidden characters or symbols  Ensure there are no hidden spaces, currency symbols typed manually, or other non-numeric characters in the input cells. Even a single non-numeric character can cause a subtraction error. 
3. Complete and valid references  Double‑check that every cell referenced in the formula contains a value and is not blank, merged incorrectly, or pointing to an error cell. 
4. Formula structure  As a quick test, try subtracting the components step‑by‑step (e.g., price minus parts cost, then subtract labor cost) to isolate which reference is causing the issue. 

Excel cannot subtract text labels like Selling_price_per_unit. It can only do math with: 

• Numbers (e.g., 100) 
• Cell references (e.g., C5, G8) 
• Named ranges (if they are properly defined) 

Right now, Excel is treating those words as plain text, so it returns #VALUE!. 

 

These are some options I recommend you try: 

Option 1 (Most common): Use cell references 

Replace the text with the actual cells that contain the numbers. 

Example (replace with your real cells): =H5 - H6 – H7 

Where: 

• H5 = Selling price per unit 
• H6 = Cost of parts per unit ($90) 
• H7 = Direct labour cost per unit ($45) 

This will immediately fix the error. 

 

Option 2: Use Named Ranges (if intended) 

If you want to use names like Selling_price_per_unit, you must define them. 

How to define named ranges: 

1. Click the cell with the selling price 
2. Go to the Name Box (left of the formula bar) 
3. Type: Selling_price_per_unit 
4. Press Enter 
5. Repeat for: 

• Cost_of_parts_per_unit 
• Direct_labour_cost_per_unit 

After that, your original formula will work. 

 

Quick check (very important) 

Your input values show $45 and $90. If those were typed with a dollar sign manually (e.g. $90 as text), Excel will also throw #VALUE!. To check: 

1. Click the cell 
2. Look at the formula bar 

• Correct: 90 
• Wrong: $90 

If wrong: 

• Remove the $ 
• Apply Currency format instead (Home > Number > Currency) 

Note: Please understand that my initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.  

I hope this information is useful. Additionally, as other users have raised similar concerns and may not be aware of where to share their feedback, I hope this response helps clarify the situation and suggests a potential next step. Highlighting this information can make it more visible to others in the community who may be facing the same issue, making it easier for them to find guidance and contribute their feedback as well. 

Thank you again for your time and for raising this important usability concern. If you have any further questions or need additional assistance, please don’t hesitate to reach out. 

I look forward to your thoughts on this.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

The #VALUE! error means Excel cannot perform the subtraction with the referenced cells because something in the formula or in those cells is not a valid numeric value.

To fix it, follow these steps:

1. Test basic subtraction
   • In a new workbook, enter 2 in A1 and 4 in B1.
   • In C1 enter =B1-A1.
   • If this also returns #VALUE!, the issue is likely with the Windows regional list separator (step 2). If it works, skip to step 3.
2. Check the Windows list separator (only if even =B1-A1 fails)
   • Open the Region control panel in Windows.
   • On the Formats tab, select Additional settings.
   • Find List separator.
   • If it is set to the minus sign (-), change it to another character such as a comma (,) or semicolon (;), depending on what is standard for the region.
   • Select OK, reopen the workbook, edit any affected formulas, and ensure minus signs are used only for subtraction.
3. Confirm the referenced cells are true numbers
   • The cells for Selling_price_per_unit, Cost_of_parts_per_unit, and Direct_labour_cost_per_unit must contain numeric values, not text.
   • If any of these cells contain text (including hidden spaces), Excel will return #VALUE! when subtracting.
4. Remove spaces or text in input cells
   • Select the cells or entire columns that the formula references.
   • On the Home tab, choose Find & Select > Replace.
   • In Find what, type a single space; leave Replace with empty.
   • Choose Replace All to remove spaces that may cause #VALUE!.
5. Re-enter the numbers if needed
   • If a cell still behaves like text (e.g., left‑aligned, or formulas don’t work with it), clear the cell and type the number again.
6. Rebuild the subtraction formula
   • In the result cell, use a simple numeric subtraction pattern, for example:
     • =Selling_price_per_unit - Cost_of_parts_per_unit - Direct_labour_cost_per_unit
   • Ensure each name or reference points to a numeric cell.

Once all referenced cells are numeric and the list separator is not a minus sign, the subtraction by cell referencing will calculate normally and the #VALUE! error will disappear.

---

References:

• How to correct a #VALUE! error