| View previous topic :: View next topic |
| Author |
Message |
AndrewHuggan
Newbie
Joined: 19 Dec 2010
Posts: 4
|
 Posted: Sun Dec 19, 2010 11:15 am Post subject: [SOLVED] If Range Contains, then this value |
 |
|
Hiya folks,
I have been trying to do this for a while and been googling for answers but just cant seem to get it right.
I have a spreadsheet where I am keeping track of my CD sales. I want the spreadsheet to workout the paypal charges automatically, depending on wheather or not the address is in the uk.
For example:
IF the addres contains "United Kingdom" (cell range H2:L2) then Paypal Charge (U2) = Total amount (T2) * 3.4% (X2) + 0.2
IF the addres does not contain "United Kingdom" (cell range H2:L2) then Paypal Charge (U2) = Total amount (T2) * 3.4% (X3) + 0.2.
I tried using the IF function, the HLOOKUP function and the LOOKUP function but I just dont seem to be able to get it right.
Here is the formula I thougtht should work but doesnt:
=IF(LOOKUP("United Kingdom";H2:L2);(T2*X3)+0.2;(T2*X2)+0.2)
Any ideas where I am going wrong?
Last edited by AndrewHuggan on Tue Dec 21, 2010 11:02 am; edited 2 times in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
AndrewHuggan
Newbie
Joined: 19 Dec 2010
Posts: 4
|
| Posted: Sun Dec 19, 2010 11:51 am Post subject: |
|
|
| I don't understand. If I were to calculate them all manually it would defeat the purpose of calc working it out automaticall. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
AndrewHuggan
Newbie
Joined: 19 Dec 2010
Posts: 4
|
| Posted: Sun Dec 19, 2010 5:04 pm Post subject: |
|
|
| So what WOULD work? |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1846
Location: Sydney, Australia
|
| Posted: Sun Dec 19, 2010 7:34 pm Post subject: |
|
|
Maybe...
| Code: | | =IF(ISNUMBER(MATCH("United Kingdom";H2:L2;0));(T2*X3)+0.2;(T2*X2)+0.2) |
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
AndrewHuggan
Newbie
Joined: 19 Dec 2010
Posts: 4
|
| Posted: Tue Dec 21, 2010 11:01 am Post subject: |
|
|
| That worked perfectly! Thank you so much Ken! |
|
| Back to top |
|
|
|
