Forum Discussion
Splitting cell values
Thanks heaps. it works for the No value in F and almost works for The Yes. It seems to miss the fact we can't claim for the amount over 200. Although it does looks like that is in the formula??
Would it be easier to do add some extra column and the remove the unit amount if F = no? (F = O in the example below)
Andy1883 In cell H2 (Non claimable distance (m)), try using the IFS function as follows:
=IFS(F2="No", G2, G2>200, G2-150, TRUE, MIN(--G2, 50))
Or, with structured table references:
=IFS([@EUL]="No", [@[Total Distance (m)]], [@[Total Distance (m)]]>200, [@[Total Distance (m)]]-150, TRUE, MIN(--[@[Total Distance (m)]], 50))
Then, in cell I2 (Claimable Distance (m)), use either =G2-H2 or =[@[Total Distance (m)]]-[@[Non claimable distance (m)]]. If that's not exactly what you were after, let me know. Cheers!
IFS Function
Thanks heaps. it works for the No value in F and almost works for The Yes. It seems to miss the fact we can't claim for the amount over 200. Although it does looks like that is in the formula??
Would it be easier to do add some extra column and the remove the unit amount if F = no? (F = O in the example below)
Andy1883 The results of the IFS function in column H of your screenshot appear to be correct. Double-check your formulas in column I (Claimable Distance). The formula in cell I13 should be =G13-H13 (212-62=150), and the formula in cell I16 should be =G16-H16 (207-57=150).
- Awesome thank you.
Column I was wrong.
works perfectly now