Forum Discussion
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
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)))
8 Replies
- Another option:
=CONCAT(ABS(MID(A1&B2,{1,2,3},1)-MID(A1&B2,{4,5,6},1))) 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)))
HansVogelaar this worked perfectly. It will always be a three digit number. Thank you so much.
With double dash in front...
OK, yes!
- Try this:
=LET(a,A1,b,B2,TEXTJOIN(,,ABS(MID(a,SEQUENCE(LEN(a)),1)-MID(b,SEQUENCE(LEN(b)),1)))*1) - 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.- Going to try this because I will need 4 digits to do the same. Thank you
