In my dim and distant memory I think I recall there is a way of nesting text (as memorandum only) in a cell formula, but it does not affect the formula calculation itself.
So e.g if I was to add two costs of £300 and £400 in a sum formula, it is simply =SUM(300,400) But if I want to add the detail in the formula that the costs were a taxi £300 and hotel £400 it was something along the lines of...
=SUM('taxi' 300, 'hotel' 400). The result of £700 would still appear as normal. Clearly this is not the correct syntax as it doesn't work but it was something similar to this.
If someone can please put me out of my misery I would be most grateful as I have been wrestling with this and cannot find solution on Excel help.
Thank you
Replies (5)
Please login or register to join the discussion.
Some thoughts
Not sure you can do that (hopefully someone will be along to prove that wrong). You use to be able to do something similar to what you want in Lotus123 by inserting text after a semicolon at the end of a formula. Might this be what you were thinking of?
Depending on the context of wht you want to achive, the following may help:
a) Use cell comments
b) Use a Text String. e.g. ="Taxi and Hotel"&sum(300,400) though this WILL display the text
c)=sum(300,400)+N("Taxi and hotel"). The N function will return 0 for text.
Thank you Paul.
I think is your solution 'C' I am looking for.
I recall the N part, but thought there was a way of associating it with each value. i.e. text, then value; text then value. I was shown it on an Excel course I attended, but it was some time ago now so my memory may be letting me down.
But this will definitely get me on my way.
Many thanks.
Named Ranges
If you had a column of taxi expenses and a column of hotel expenses, you could name the column of taxi expenses 'taxi' and the column of hotel expenses 'hotel' and then you could get the total of taxi and hotel expenses with the formula =SUM(taxi,hotel).