Forum Discussion
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 wo...
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)))
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.
=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
