Airsoft Forums UK

what are ye trying to do?

I'm trying to work out how much a fee would be, +20p. However, I've got 2 different fees. One is 2.1%, one is 2.9%. So depending on how they pay, depends on the fee charged. I've worked out how to get excel to display which fee it'll be, I.e 2.1 or 2.9 but can't get it to work out either 2.1/2.9+20p

If that makes sense

I'm no expert, I mainly fiddle in Google sheets a bit, but I think the commands are similar. Think if you had the cost in column A and the fee percentage in column B you could use something like =ArrayFormula(IF(ISBLANK($A$2:$A),"",$A2:A+(A2:A*B2:B/100))+0.2)

Which should fill down the page.

is the data input the price and payment method? because a simple if statement would work.

lets say we have column a with the payment amount, and column b is the payment type:

=if(b1="whatever the payment type cell for 2.1% says inside the quotation", a1*0.021, a1*0.029)+0.2

quotation marks let you use a text string (eg if the box says mastercard or visa for the 2 different payment types) however it does need to be exact so typo's or capitalisation are important.

if the percentage change is for a value (eg orders under £100 are 2.9%, orders above are 2.1%) then it'd be something like:

=if(a1<100,a1*0.021,a1*0.029)+0.2

you can see the common theme, it's just different criteria for what you want to decide if it's 2.1% or 2.9%

@Adolf Hamster Currently this is working, however I need a second option in the marks, and that seems to stop it working.

=IF(J4="MASTERCARD", E4*0.021)+0.2 is what I currently have

I need another one in there for "J4=PAYPAL" 

I have tried =IF(J4="MASTERCARD", E4*0.021 (J4="PAYPAL", E4*0.029))+0.2 = but its not having it

if it's exclusively only paypal and mastercard then:

=IF(J4="MASTERCARD", E4*0.021,E4*0.029)+0.2

essentially all values that do not satisfy the criteria of j4=mastercard it will apply the paypal correction to. this will include entries where mastercard is misspelled/not capitalised exactly as the formula requires

alternately you could do:

=IF(J4="MASTERCARD", E4*0.021,if(j4="paypal",E4*0.029,"error"))+0.2

if the cell is mastercard it will apply the 0.021, if the cell is paypal it will apply the 0.029, if the cell is neither mastercard or paypal (eg if there's a spelling mistake, no capitals, or no data) it will return the word error

Love you.

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