Excel Formula Help - combining with sentence

aj9648

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?

kuratowski

="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

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)

Tigsteroonie

This just worked for me:

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

aj9648

Thanks - that's great. How do I make sure the result in formula is always only 2 dp?

CreditCardJunkie

You can use the concagnate function, Google it

aj9648

CreditCardJunkie wrote: »

You can use the concagnate function, Google it

you mean CONCATENATE

unforeseen

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

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!

tempus_fugit

unforeseen wrote: »

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

From https://patents.justia.com/patent/20110307171

Priceless.