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 is over 150 then the remaining needs to go back to H and the rest is multiplied by $10 for billing.
Situation, we install a cable 220M. We cover the first 50m as part of a deal, the 51m to 200m is covered by a 3rd party and we cover the remaining 201 to 220. I can do this with some maths but if F = no then we cover the total amount.
Hopefully this makes sense
Cheers
@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!
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).
Accepted Solutions
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)