Forum Discussion
Calculating the "spread" between multiple numbers in one cell.
Is there a way to calculate the difference between multiple numbers in one cell that are separated by a forward slash? This is for a spreadsheet that I'm using to track stock option trades. The cell ...
With such a value in A2:
=LET(a, TEXTSPLIT(A2, "/"), SWITCH(COUNTA(a), 2, ABS(INDEX(a, 2)-INDEX(a, 1)), 3, MAX(ABS(INDEX(a, 2)-INDEX(a, 1)), ABS(INDEX(a, 3)-INDEX(a, 2))), 4, MAX(ABS(INDEX(a, 2)-INDEX(a, 1)), ABS(INDEX(a, 4)-INDEX(a, 3))), "-error-"))
Thanks for the advice, the formula worked perfectly!