Robert James

I spent way longer messing about with this in Sheets than I'd like to admit because I was trying to figure out a way to do it in just one line without copying a formula down. I tried a version using IFS (instead of nested IF), which looked like this

ifs(B2="paypal",sum(A2+(A2*0.021)+0.2),B2="Mastercard",sum(A2+(A2*0.029)+0.2))

But I couldn't get it to work with ArrayFormula so it was still a copy and paste.

Then I decided to see if I could do it with a Query, but I couldn't figure out a way to apply two different calculations in the same query based on different selections. Anyway, in the end the way I did it was to do two separate Queries and then join them together (and chuck them within a Sort). This gives a one liner that should give results regardless of how many extra rows you add.

For the example below I had 3 columns -

A - Order Number

B - Payment Amount

C - Payment Method.

=sort({query(A2:C,"select A,B,C, B*1.029 where LOWER(C) matches 'paypal' label B*1.029''");query(A2:C,"select A,B,C, B*1.021 where LOWER(C) matches 'mastercard' label B*1.021 ''")})

The LOWER statement converts the text in C into lower case, and I proved this worked in testing. The LABEL part stops the calculations autogenerating a header. Anyway, this is all almost certainly irrelevant as you'll have used Hamster's version already. As with everything in Sheets/Excel there's always about 17 ways to approach the same problem.

(Edited to add, forgot to put the +0.2 for the 20p in the query, but you get the idea, would just change to select A,B,C, (B*1.029)+0.2 and edit the label accordingly

yep, there's always more than 1 way of doing things in excel.

personally i'm a fan for more complicated schemes of having a bunch of colums each with a single if that will return 1 if true and 0 if false.

then you can do whatever math you like to plot out the logic and hide the colums of if's.

this probably explains why some of my spreadsheets are barely able to function without crashing

Oh yeah, I always have to do all mine in sections of "Does this query work to just get the results". OK, then add a bit and see where it goes wrong. Which it inevitably will because you've missed a comma, or are trying to calculate months in a query (which works different from months outside a query) and so on.

But, once you get the building blocks it becomes very easy to reorder your whole set of answers by just editing one line.

I also find I have to make a document with all the little use cases I found (how do I compare two lists for VIPS, how do I find anything that's not been signed into for more than 3 months from today, how do I find the last time a customer contacted us based on their name) and show the working so I've got it for next time.

Aaanyway, enough of that, back to buggering up this 249 gearbox...