Mac Numbers

[camioneur]

Can anyone show me a formula that would return 3 different values in a cell based on the text content of another cell containing 3 different text values in apple numbers please? For example..in cells A1 to A4

A1 would either be a customer (1) "in the UK" or (2) "not in the UK" or (3) an empty cell
A2 would be the invoice net value
A3 would be the A2 x 20% if customer was in the UK
A4 would be the sum of A2+A3 or a blank or empty cell if A1 was also empty

I can return a zero for a blank A1 but I'd like to return the "" option where the cell is left blank for option 3

Any help appreciated, thanks

[Pigletion]

Try this - you might have to alter slightly if I did not follow your logic, but it returns "" if A1 is not equal to "in the UK"

A3=IF(A1="In the UK",A2*20%,IF(OR(A1="Not in the UK",A1=""),""))

A4=if(A1="In the UK",A2+A3,"")

[Pigletion]

I should have added that the first formula could be a simple if/else like the second one, but wanted to show the concept of nested ifs (just in case!). First formula could just read A3=IF(A1="In the UK",A2*20%,""). It depends how many elses you need.

[camioneur]

Thanks Pigletion, I'm new to Numbers and although i can see the logic in what you've said I still can't make it work and get the dreaded red triangle.Is there a way to post or send you a part of my sheet so you can see where I'm going wrong?

[Pigletion]

What formula are you typing into cell A3? With text, you need to enclose it in "" and make sure it matches exactly (upper/lower case shouldn't matter, but spacing will).

[camioneur]

I've sorted it thanks to you Pigletion, there was an existing formula elsewhere in the sheet that refered to one of the cells I was trying to use in your formula. What Excel would have called a circular reference. As soon as I deleted it, your formula worked a treat.. Thanks again

[Pigletion]

Good news! Glad to have helped.