Subtracting Text from Number

[Guest]

I am working in a large spreadsheet and need to be able to subtract to
columns without adding any new columns or using subtotals. If I have words
and numbers in the columns, ex. "none", is there a function that will
eliminate the "value" error message when I subtract (ex. 9-none = value, but
i want none or any text to be viewed as zero)?

Ex. of how it is currently (not) working
none - 9 = value (wrong)
12 - 9 = 3 (right)

Ex. of how I would like for it to work
none - 9 = -9 (right)
12 - 9 = 3 (right)

 

[Rick Rothstein \(MVP - VB\)]

I am working in a large spreadsheet and need to be able to subtract to

columns without adding any new columns or using subtotals. If I have
words
and numbers in the columns, ex. "none", is there a function that will
eliminate the "value" error message when I subtract (ex. 9-none = value,
but
i want none or any text to be viewed as zero)?

Ex. of how it is currently (not) working
none - 9 = value (wrong)
12 - 9 = 3 (right)

Ex. of how I would like for it to work
none - 9 = -9 (right)
12 - 9 = 3 (right)

Assuming for this example that your data is in A1 and B1 and C1 is your
subtraction column... use this formula in C1...

=IF(A1="none",0,A1)-B1

If B1 could also have "none" in it, then use this...

=IF(A1="none",0,A1)-IF(B1="none",0,B1)

Note that the comparisons will be case sensitive.

Rick

 

[Bernie Deitrick]

You need to use an if function: if None could be in cell A3:

=IF(ISNUMBER(A3),A3,0) - 9

If you have a lot of words that equal certain values, then you could create a table

None 0
One 1
Two 2
Some 4
Many 100

use

Vlookup(A3,TableRange,2,False)

in place of the 0 in the first formula.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Does this do what you want?

=IF(ISERROR(A1-B1),B1*-1,A1-B1)

Mike

 

[Guest]

=IF(ISNUMBER(A1),A1,0)-IF(ISNUMBER(B1),B1,0)

 

[Ragdyer]

First of all, are you using "legal" XL formulas?

=12-9

=none-9

If you're using the above type of syntax, then you *can* assign values to
words, or ... more precisely ... assign names to values.

To label zero as "none",
<Insert> <Name> <Define>

In the "Names In Workbook" box on top, type
none
In the "Refers To" box at the bottom, change whatever's there to:
=0
Then <OK>

NOW, in any cell, enter:
=none
And you'll see a return of 0.

If you enter this formula in a cell:
=none-9

You'll get your return of
-9

You can also assign names to formulas.
Say you wanted "all" to mean the total of A1 to A5.

Use the above procedure, entering
all
in the "Names In Workbook" box,
And, in the "Refers To" box, enter:
=Sum($A$1:$A$5)

Now, with A1 to A5 containing 1 to 5 respectively, enter
=all
in any cell, and get a return of 15.

You can now use a formula something like this:
=none-9+all
to get a return of 6.

 

[Guest]

Assume A1 and B1 either have text or positive value

=MAX(A1,0)-MAX(B1,0)