Solved need correct formula for subtraction

November 6, 2013 at 10:11:23
Specs: Windows XP
2,254.79 (k19)
3733.84 (k20)
83.01 D (k21)
1.15 D (k22)
84.16 D (k23)
need formula to subtract 84.16 from 3733.84

See More: need correct formula for subtraction

Report •

November 6, 2013 at 10:31:06
Unless mistaken?


this seems like it should work


Report •

November 6, 2013 at 11:48:30
✔ Best Answer
There are a few issues with mikelinus's suggestion...

1 - A simple subtraction operation does not need the SUM function.

Typically =K23-K20 would be all that is needed, but not in this case for 2 reasons. Read on...

2 - "need formula to subtract 84.16 from 3733.84"

That would be written as =K20-K23, the opposite of what mikelinus suggested, but I don't think that will work either. Read on...

3 - If the "space D" is actually in K23, then you can't use a simple subtraction formula. The "space D" will cause Excel to see the contents of K23 as Text. You need to extract the number portion before you can perform any mathematical operations.

Try this:

=K20-LEFT(K23,FIND(" ",K23)-1)

That formula will use the FIND function to extract the characters before the "space". Once those characters are extracted, Excel will treat them as a number, not Text.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

November 6, 2013 at 13:08:43
apologies to the original poster and DerbyDad03

I interpreted the data as

...	K	   L
19	2,254.79 
20	3733.84
21	83.01	   D 
22	1.15 	   D
23	84.16 	   D 

And then promptly failed to apply reason in the subtraction.


Report •

Related Solutions

Ask Question