SOLVED

Subtraction Help

Occasional Contributor

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
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.
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

@Queen_Bee 

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)))

@Hans Vogelaar 

With double dash in front...

@Sergei Baklan 

OK, yes!

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

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

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