Copying values/numbers instead of cell references

**Jpdparadis** · 12-15-2010, 02:36 PM

Hi

I'm not a novice at Excel - but i have bumped into a problem.

I'm a student at a University in Denmark, and i have a test in a couple of days. The topic is calculation of firm values based on given information.

Therefor I have got this massive spreadsheet in Excel. The problem is, that I have to show my calculations explicit (in numbers). As the calculation is based on formulas (cell references) it is very hard (and time consuming) to find the values of each cell reference and type them.
An example could be: =C129/(B116-B115)/(1-(E114*B117*B138)/(B116-B115)) - and the underlying values/numbers could be: 1/(2-3)/(1-(4*5*6)/(1-2)).

It would be nice, if i could just copy the looong formula with cell references into the cell next to - and then Excel would automatically find the underlying numbers and type them. If you know what i mean??

Thanks in advance
- Jesper Pedersen

**DonkeyOte** · 12-16-2010, 09:27 AM

Jesper, welcome to the board, an interesting question.

I'm sure there are clever ways of doing this but to be honest I wouldn't lay claim to knowing them myself - in reality what you're looking to do is evaluate each variable and replace with resulting constant (per F9 functionality in the Formula Bar).

Based on the very basic example you offer, if it's that trivial:

Please Login or Register  to view this content.

the FnK function can then be called along the lines of:

Please Login or Register  to view this content.

where A1 contains the actual formula of interest

note: in the above (much simplified scenario) we are assuming all range references to be local to the active sheet and an environment prior to XL2007 (if not change {1,2} to {1,2,3})

so to reiterate this is meant as nothing more than a possible way forward re: future development.

edit: further major caveat - the above is per the example looking at references as single cell references and is not catering for ranges (eg B115:B120) - to reiterate this is not intended to be an all singing all dancing solution

a more robust solution would be to iterate the formula string based on known delimiters (+,-,(, ) etc...) and determine each component part (where running balance of delimiters is 0) evaluating each in turn.

**Jpdparadis** · 12-16-2010, 10:36 AM

Hi again
Thanks for your answer

As i am new to this forum, i'm a little bit confused of what information you are giving me in your reply. Have you done some programming, which I should import to my Excel?
By the way - i've got Excel 2011 (for Mac).

I have attached a screenshot of my "problem/situation".
As you'll see, i have got the result of my formula in the red box (12,84%) - named 2.
The formula (cell references) can be seen in the formula line (red box - named 1). I have then written the numbers MANUALLY (that the cells refers to) on the right hand side of the result. I have called this red box 3. The reason why i need to write the numbers, is that my professor needs to see calculation methods in numbers - and not cell references.

What i would like, is to copy the cell references (red box 1) to the right hand side of the result (12,84%) - and then Excel would automatically write the numbers that the formula consists of - instead of the cell references or the result.

Are there any way i can make excel do that for me?

- Jesper

**Jpdparadis** · 12-19-2010, 01:01 PM

I may have to BUMP this one

**martindwilson** · 12-19-2010, 02:57 PM

if you just want to display it
="("&D91&"/"&G81&")+"&C53*100&"%"

**Jpdparadis** · 12-20-2010, 09:39 AM

Nice one, martindwilson.
That worked.

Are there any "rules" when displaying the formulas - i mean - you've added a lot of quotes (") and ampersands (&). But i can not see the logic?

Kind regards
- Jesper