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 in question can contain up to 4 values (i.e. option strike prices) with each value separated by a forward slash. For example: "235/240/310/315". I want to create a formula that will calculate the "spread" between the 1st two numbers and the last two numbers and return the larger of the two values. In this example I need the value of 5 returned.
There will be other times that the cell may contain three values where I need the difference between the 1st & 2nd numbers and then the 2nd & 3rd numbers, again returning the larger of the two values. For example: "37/44/50" where the value of 7 is returned.
Thank you in advance for any solutions/suggestions.
3 Replies
As variant
=LET(a, TEXTSPLIT(A2, "/"), MAX( ABS( TOROW(CHOOSECOLS(a - DROP(a,,1), {1,3}),2) ) ) )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!
