Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    This eco-post is made of recycled electrons

  3. #3
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).
    This eco-post is made of recycled electrons

  5. #5
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by steve pratt View Post
    Are you actually in Serbia?
    Yes I am!
    This eco-post is made of recycled electrons

  7. #7
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    WOW! Hope you're safe... keep your head down.
    Thanks again.

  8. #8
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    This eco-post is made of recycled electrons

  9. #9
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    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.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  10. #10
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by t8ntlikly View Post
    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
    This eco-post is made of recycled electrons

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could amend the formula to:
    =(B24-E24)/2
    and then apply different custom formats for positive and negative numbers.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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