How to Fix Excel #VALUE! Error When Multiplying TRUE by 1
Question details
The user encounters a #VALUE! error in Excel when trying to multiply TRUE or FALSE 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.
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.
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.
Click on the cell displaying the #VALUE! error to reveal the formula in the Formula Bar.
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).
Ensure the formula is multiplied by 1: =IF(D14="Pass",TRUE,FALSE)*1.
Press Enter to update the cell. Excel will successfully output 1 for TRUE and 0 for FALSE.

Convert Condition Directly to Numbers
Bypass logical text completely by commanding the IF function to output 1 or 0 directly, avoiding the need for multiplication.
Use Direct Multiplication or Double Unary Operator
Evaluate the expression directly without an IF function by coercing the boolean result into a number natively.
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. Open your file: Launch WPS Spreadsheet and open the document containing the #VALUE! error.
- 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. Correct the text string: Check the Formula Bar and remove any quotation marks around "TRUE" or "FALSE".
- 4. Apply formula: Press Enter to instantly apply the corrected formula and view the proper numeric result.

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.




