Forum Discussion
Andy1883
Jan 26, 2024Copper Contributor
Splitting cell values
Hi Guys This will be easy for someone I am sure but my brain is checking out. need to copy the value of G to H if F is no. if F is Yes then need minus 50 from it and put it in I. If I ...
- Jan 28, 2024
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)
djclements
Jan 26, 2024Bronze Contributor
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
- Andy1883Jan 28, 2024Copper Contributor
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)
- djclementsJan 29, 2024Bronze Contributor
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).
- Andy1883Jan 30, 2024Copper ContributorAwesome thank you.
Column I was wrong.
works perfectly now