 SOLVED

# Subtraction Help

I'm looking for a formula to return certain values when using excel. For instance:

Cell A1 would be 531

Cell B2 would be 185

I want to subtract but I want it to return 454 as the answer. So it would basically ignore the negative answer that should be returned. And it just focuses on the digit in each individual spot versus the number as a whole. 5 -1, 3-8 =8 - 3, and 1 - 5 = 8- 3

8 Replies

# Re: Subtraction Help

That's some pretty creative subtraction there Queen_Bee! Here's your formula:
=LET(
value1, 531,
value2, 185,
maxlen, MAX(LEN(value1), LEN(value2)),
digits1, MID(TEXT(value1, REPT("0", maxlen)), SEQUENCE(maxlen), 1),
digits2, MID(TEXT(value2, REPT("0", maxlen)), SEQUENCE(maxlen), 1),
absdiff, ABS(digits1 - digits2),
VALUE(TEXTJOIN("", TRUE, absdiff))
)

You can replace the numbers with cell references. It also handles if there are a different number of digits, so 531 and 1185 returns 1454.

# Re: Subtraction Help

Try this:
=LET(a,A1,b,B2,TEXTJOIN(,,ABS(MID(a,SEQUENCE(LEN(a)),1)-MID(b,SEQUENCE(LEN(b)),1)))*1)
best response confirmed by Queen_Bee (Occasional Contributor)
Solution

# Re: Subtraction Help

If you're certain the two numbers have the same number of digits, a slightly shorter version:

=CONCAT(ABS(MID(A1,SEQUENCE(LEN(A1)),1)-MID(B2,SEQUENCE(LEN(A1)),1)))

# Re: Subtraction Help

With double dash in front...

OK, yes!

# Re: Subtraction Help

Another option:
=CONCAT(ABS(MID(A1&B2,{1,2,3},1)-MID(A1&B2,{4,5,6},1)))

# Re: Subtraction Help

@Hans Vogelaar this worked perfectly. It will always be a three digit number. Thank you so much.

# Re: Subtraction Help

Going to try this because I will need 4 digits to do the same. Thank you