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
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.
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?
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).
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