We’d like to remind Forumites to please avoid political debate on the Forum.

This is to keep it a safe and useful space for MoneySaving discussions. Threads that are – or become – political in nature may be removed in line with the Forum’s rules. Thank you for your understanding.

📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
The Forum now has a brand new text editor, adding a bunch of handy features to use when creating posts. Read more in our how-to guide

Excel Formula Help - combining with sentence

aj9648
aj9648 Posts: 1,391 Forumite
Part of the Furniture 500 Posts
in Techie Stuff
Hi

Struggling with combining text and formulas in excel. I have in cell A1 £165.97 and in A2 £0. Cell B1 is £20.03 and in B2 £0. I am trying to automate the sentence:

Basic gross pay above is inclusive of £20.03 (£165.97 plus 12.07%) holiday pay.

I have so far got the following:

="Basic gross pay above is inclusive of "&TEXT(A1+A2,"0.00")&" (B1+B2 plus 12.07%) holiday pay.

But the B1+B2 formula does not seem to work and also how do I get the £symbol in front of each?

Any excel experts?

Comments

  • kuratowski
    kuratowski Posts: 1,415 Forumite
    Seventh Anniversary 1,000 Posts Name Dropper Photogenic 
    ="Basic gross pay above is inclusive of £"&TEXT(A1+A2,"0.00")&" (£"&TEXT(B1+B2,"0.00")&" plus 12.07%) holiday pay."

    If you are going to do this kind of thing a lot, a good tip is to use helper columns to store interim workings, it makes the formulas easier to write and understand. For example, you could put ="£"&TEXT(A1+A2,"0.00") into its own cell (say C1) and then refer to that in your sentence formula.
  • Tigsteroonie
    Tigsteroonie Posts: 24,954 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    Are your As and Bs the wrong way round to achieve what you want?

    Put the £ inside your text strings, i.e starting:

    ="Basic gross pay above is inclusive of £"

    (I'll get the rest of it in a moment)
    :heartpuls Mrs Marleyboy :heartpuls

    MSE: many of the benefits of a helpful family, without disadvantages like having to compete for the tv remote

    :) Proud Parents to an Aut-some son :)
  • Tigsteroonie
    Tigsteroonie Posts: 24,954 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    This just worked for me:

    ="Basic gross pay above is inclusive of £"&SUM(A1:A2)&" (£"&SUM(B1:B2)&" plus 12.07%) holiday pay."
    :heartpuls Mrs Marleyboy :heartpuls

    MSE: many of the benefits of a helpful family, without disadvantages like having to compete for the tv remote

    :) Proud Parents to an Aut-some son :)
  • aj9648
    aj9648 Posts: 1,391 Forumite
    Part of the Furniture 500 Posts
    Thanks - that's great. How do I make sure the result in formula is always only 2 dp?
  • CreditCardJunkie
    CreditCardJunkie Posts: 662 Forumite
    Eighth Anniversary 500 Posts Name Dropper
    You can use the concagnate function, Google it :)
    Debt Free as of December 2020 👏

    MFW - 40 months shaved off the mortgage
  • aj9648
    aj9648 Posts: 1,391 Forumite
    Part of the Furniture 500 Posts
    CreditCardJunkie wrote: »
    You can use the concagnate function, Google it :)

    you mean CONCATENATE
  • unforeseen
    unforeseen Posts: 7,450 Forumite
    Part of the Furniture 1,000 Posts Photogenic Name Dropper
    aj9648 wrote: »
    you mean CONCATENATE

    Who knows. Somebody has used the word concagnation in a few patent applications

    From https://patents.justia.com/patent/20110307171
    The process essentially concactinates co-linear segments and breaks the concagnation at points where the vehicle has turned.
  • Tigsteroonie
    Tigsteroonie Posts: 24,954 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    aj9648 wrote: »
    Thanks - that's great. How do I make sure the result in formula is always only 2 dp?
    Go with Kuratowski's suggestion of having the sum somewhere else and fixing that to two decimal places before your full sentence?

    Sorry, brain switched off from Excel at midnight and I don't have to think about it again until tomorrow now!
    :heartpuls Mrs Marleyboy :heartpuls

    MSE: many of the benefits of a helpful family, without disadvantages like having to compete for the tv remote

    :) Proud Parents to an Aut-some son :)
  • tempus_fugit
    tempus_fugit Posts: 1,189 Forumite
    Eighth Anniversary 1,000 Posts Name Dropper Photogenic
    unforeseen wrote: »
    Who knows. Somebody has used the word concagnation in a few patent applications

    From https://patents.justia.com/patent/20110307171
    Priceless. :D
    Retired at age 56 after having "light bulb moment" due to reading MSE and its forums. Have been converted to the "budget to zero" concept and use YNAB for all monthly budgeting and long term goals.
This discussion has been closed.
★ ★ ★ Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 353.5K Banking & Borrowing
  • 254.2K Reduce Debt & Boost Income
  • 455.1K Spending & Discounts
  • 246.6K Work, Benefits & Business
  • 603K Mortgages, Homes & Bills
  • 178.1K Life & Family
  • 260.6K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16K Discuss & Feedback
  • 37.7K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.
Update Your Picture