nesting text in a cell formula

nesting text in a cell formula

Didn't find your answer?

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.

avatar
By paulwakefield1
29th Oct 2012 11:37

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.

Thanks (0)
Replying to debrahuzzard:
avatar
By User deleted
29th Oct 2012 12:04

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.

Thanks (0)
By Steve Kesby
29th Oct 2012 11:49

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).

Thanks (0)
avatar
By paulwakefield1
29th Oct 2012 12:26

This might be the variation you are thinking of?

=SUM(N("taxi"),300,N("hotel"),400)

Thanks (0)
Replying to redman7:
avatar
By User deleted
29th Oct 2012 13:40

That the one - thanks!

Thanks (0)