1

So I've been searching online for what seems like forever to try and locate a way to achieve this in Google Sheets. The aim here is to choose a currency and sale type using data validation in two cells and run a function depending on the values of both those cells in a third cell.

A1 is data validation to select a currency (GBP, USD, EUR, etc.). B1 is data validation to select a transaction (Sale, Refund). C1 outputs the transacted value.

D1 holds price. E1 holds fee cost. F1-F3 hold currency conversions.

Here's how it needs to work: If B1 is a sale, a function needs to run to calculate the total transaction, adding a fee. If B1 is a refund, the same function needs to run, but to subtract a fee. This all then takes into account the conversion rate for the currency selected. So I essentially require Sheets to check the currency, check whether it's a sale or refund, then run the function.

Toying with IF this seems to be far too complex with two functions per currency required, depending on whether it's a sale or refund. It's at a point where my brain is turning to mush and I ended up with the following, which of course doesn't work.

=lookup(A1,
       {"GBP","EUR","USD"},
       {if(B1="Sale",D1+E1*F1,if(B1="Refund",D1-E1*F1)), 
        if(B1="Sale",D1+E1*F2,if(B1="Refund",D1-E1*F2)), 
        if(B1="Sale",D1+E1*F3,if(B1="Refund",D1-E1*F3))}
)

Praying that someone is able to slap me down for my stupidity and point me in the correct direction.

0

You need the lookup only for the exchange rate, and the difference between adding and subtracting a term can also be accomplished by multiplying with 1 or -1 and always adding it. If you're certain B1 always contains either "Sale" or "Refund", you can go for:

=D1+(IF(B1="Sale",1,-1))*E1*LOOKUP(A1,{"GBP","EUR","USD"},{F1,F2,F3})

but it looks like your current formula produces FALSE if it isn't, and then you'll need something like this:

=IF(OR(B1="Sale",B1="Refund"),D1+(IF(B1="Sale",1,-1))*E1*LOOKUP(A1,{"GBP","EUR","USD"},{F1,F2,F3}))
2
  • Was looking over another attempt I made earlier, which almost worked. Is there a reason as to why the F2 cell would be ignored in the below formula for both Sale and Import validations? Google Sheets correctly calculates everything but ignores the F2 cell. =IFS(A2="Import",C2*B2+F2,A2="Sale",B2*C2-F2)*IFS(G2="GBP",Data!B$31,G2="EUR",H2,G2="USD",H2) Cheers!
    – Traydash
    Jun 9 '20 at 17:24
  • @Traydash we'll probably need a demo sheet from you to find out. I'm not that good at debugging formulas by sight.
    – Glorfindel
    Jun 9 '20 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.