1. Let's say that 2 people went on vacation and agreed to split everything 50/50. They each kept receipts and when the vacation was over, it was time to see who owed who money.

I have 2 columns of dollar values. The first column is Traveler A (the sum is diplayed in B24). The second column is Traveler B, (the sum is displayed in E24).

I have a very simple formula that will test to see which total is larger and how to calculate the money owed... but I would like to add a prefix, like "Traveler A owes [calculated value]"

My formula now is as shown below. How would I add a (prefix) text in this formula:
=IF(B24>E24,((B24-E24)/2),(E24-B24)/2)

I was thinking something like this, but it obviously doesn't work:
=IF(B24>E24,"Traveler B owes"((B24-E24)/2),"Traveler A owes"(E24-B24)/2)

Thanks,
Steve

2. You were not far from the solution tough. To concatenate texts, use the symbol &. So your formula should be:
=IF(B24>E24,"Traveler B owes " & ((B24-E24)/2),"Traveler A owes " & (E24-B24)/2)

3. Stephane,

Cool... I was reading about the "&" symbol for this situation late last night... but I guess I didn't give it enough time. Thanks. Now.... I can't seem to format the cell to provide the result in currency.

I was reading here where you had a similar question about 4 years ago... where you received some advice on using a custom formatting. I've been trying several of those ideas, but can't seem to make them work. I haven't modified the formula... but tried to use the following as a custom format without success. Any suggestions:
\$ #.00

4. Suggestion:
=IF(B24>E24,"Traveler B owes ","Traveler A owes ") & TEXT(ABS((B24-E24)/2),"\$#,##0.00")
Note that the calculation and formatting is outside the IF in ordee to avoid copying it twice, and I use the ABSolute value (the greatest between a number and its opposite value).

5. Stephane... Thanks again, that works GREAT.... I'll have to study this a little closer, since you took my simple formula to another level.
Are you actually in Serbia?

6. Originally Posted by steve pratt
Are you actually in Serbia?
Yes I am!

Thanks again.

8. No need to keep my head down. Serbia has been a peaceful country since the war 8 years ago and I have never felt unsafe in that country. Of course, like in every city in the world, there are some places where you shouldn't go during the night.

9. In Custom formatting you can do something like this as well \$#,##0.00 "xx" where xx = your input.
I learned this from this lounge a few years back and still use it today.

10. Originally Posted by t8ntlikly
In Custom formatting you can do something like this as well \$#,##0.00 "xx" where xx = your input.
I learned this from this lounge a few years back and still use it today.
You are right, this is a valid technique to show a unit of measure for example and still have the possibility to add numbers. However, in the present case, the text changes depending on who is the debtor, so I am not sure how you can apply it

11. You could amend the formula to:
=(B24-E24)/2
and then apply different custom formats for positive and negative numbers.
FWIW.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•