Donate SIGN UP

Mac Numbers

Avatar Image
camioneur | 10:13 Thu 10th Nov 2016 | Computers
6 Answers
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

Gravatar

Answers

1 to 6 of 6rss feed

Best Answer

No best answer has yet been selected by camioneur. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
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,"")
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.
Question Author
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).
Question Author
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
Good news! Glad to have helped.

1 to 6 of 6rss feed

Do you know the answer?

Mac Numbers

Answer Question >>