Hi There,
is it possible to add some text to a formula in one cell , Iam trying to add up a range of cells , and add some text to it
my original sum is =Sum(a1:a12) the result of which says £70.00 , but i would like it to say “Year Total = £70.00”
look forward to your help … thanks
="Year Total = £" & TEXT(SUM(A1:A12);"0.00") ?
1 Like
Thats fab , exactly what i was trying to do … Cheers …
@Runcom_Rat: you closed the question but didn’t accept any answer. Do you consider @JohnSUN’s comment as your preferred answer? If so, say it explicitly so that he can turn it into a formal answer which can be accepted for community benefit.
Question reopened.
@Runcorn_Rat:
Anyway you need to understand what makes the relevant difference between the results you get depending on whether you follow @JohnSUN or @ajlittoz:
In the first case you get a text (string). You won’t be able tu use it in continuing calculations.
In the second case you get a formatted number, and if you refer to the cell containing it, you get returned the value as an operand for your formula.
Why of interest?
If you have the sum =SUM(B1:B12) in a different cell, say the first sum in A21, and the second one in B21, you may want to add both the values in, say D21 like with =A21+B21. This will only work if you follow the advice by @ajlittoz.
Beyond this I would strongly confirm the suggestion by @ajlittoz to use an adjacent cell for this kind of caption…
In addition to @JohnSUN’s suggestion, you can play with cell formatting.
Keep the =SUM(A1:A12) unaltered so that you can chain-compute with the formula result.
Select the cell and Format>Cell. It is probably already formatted as a “currency” cell with format code like [$£-809]# ##0,00;[RED]-[$£-809]# ##0,00. Change it to:
"Year Total = "[$£-809]# ##0,00;"Year Total = "[RED]-[$£-809]# ##0,00
However, the best solution is to put captions like “Year Total =” in a separate cell for maximum versatility and ease of maintenance.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.
@ajlittoz < thats great information will try it out, and see how i get on , as there has been times as you stated i need to use the cell from a previous calculation … thanks …
This is not another answer to your question.
Please click on the more link under your “non-answer” to repost as a comment under my answer. Only you as owner of the “non-answer” can do that. This site is not a forum and the nearest equivalent to a conversation is comments.
For completeness, the 809 in [$£-809] is the MS locale ID for en-GB, so if the £ currency symbol is used in another locale that ID should be adjusted to the proper value, for example 452 for cy-GB or 43C for gd-GB. Just use the same value that is present in the default currency format for your locale.