Formula Errors

How to Fix Excel #VALUE! Error When Multiplying TRUE by 1

Camila MilosovichCamila Milosovich Nov 2, 2026 869 views

Question details

The user encounters a #VALUE! error in Excel when trying to multiply TRUE or FALSE by 1.

How to Fix the Excel #VALUE! Error When Multiplying TRUE by 1
Product
Excel
Device & OS
not provided
Scenario
Attempting to convert boolean outputs to 1s and 0s using multiplication, but the logical values are formatted as text strings rather than actual booleans.
Observed behavior
Excel returns a #VALUE! error because text strings such as "True" or "False" cannot be multiplied by numerical values.
Before you start

Check the alignment of the TRUE or FALSE value in your cell. By default, text strings align to the left, while recognized logical values are centered automatically.

Solution 1Recommended

Return Logical Values Without Quotation Marks

Fix your IF formula by removing quotation marks around TRUE and FALSE so Excel recognizes them as logical booleans instead of uncalculatable text strings.

When you wrap TRUE or FALSE in quotation marks (e.g., "TRUE"), Excel treats them as standard text. Multiplying text by a number triggers the #VALUE! error. Removing the quotes allows Excel to process the boolean logic mathematically.

1
Locate the formula

Click on the cell displaying the #VALUE! error to reveal the formula in the Formula Bar.

2
Remove quotation marks

Edit the formula to remove quotation marks around the logical values. For example, change =IF(D14="Pass","TRUE","FALSE") to =IF(D14="Pass",TRUE,FALSE).

3
Multiply by 1

Ensure the formula is multiplied by 1: =IF(D14="Pass",TRUE,FALSE)*1.

4
Apply changes

Press Enter to update the cell. Excel will successfully output 1 for TRUE and 0 for FALSE.

Return Logical Values Without Quotation Marks
Tip: Recognized logical values will usually automatically capitalize themselves in the Formula Bar once the quotation marks are removed.
Efficient Formula Troubleshooting

Fix Formula Errors Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides robust error-checking tools and full compatibility with Excel functions, making it easy to spot and resolve data type mismatches like the #VALUE! error.

  1. 1. Open your file: Launch WPS Spreadsheet and open the document containing the #VALUE! error.
  2. 2. Identify the error: Click on the cell displaying the #VALUE! error. WPS will display a warning icon; hover over it to read the error details.
  3. 3. Correct the text string: Check the Formula Bar and remove any quotation marks around "TRUE" or "FALSE".
  4. 4. Apply formula: Press Enter to instantly apply the corrected formula and view the proper numeric result.
100% compatible with Microsoft Excel formulas and file formats.Intuitive error tracing to quickly spot the cause of #VALUE! errors.Lightweight software with fast processing for large datasets.Completely free to use for daily office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show #VALUE! when multiplying text by a number?

Excel expects numerical values for mathematical operations. Text strings, even if they spell out "True" or "False", cannot be calculated mathematically, resulting in a Value Error.

How can I tell if my TRUE/FALSE values are text or logical?

By default, text is left-aligned in an Excel cell. Recognized logical values (TRUE/FALSE) are centered by default and automatically capitalized by the system.

What does the double hyphen (--) do in an Excel formula?

The double hyphen is known as a double unary operator. It is a mathematical shortcut that coerces boolean logical values (TRUE or FALSE) into their numeric equivalents (1 and 0).

Can I use the N() function instead of multiplying by 1?

Yes, the N() function converts non-numbers to numbers, dates to serial numbers, and logical TRUE/FALSE to 1/0. For example, =N(D14="Pass") will cleanly return 1 if true and 0 if false.