0

For example I have in one row pretax income and tax paid, then to the right I have a number representing them, say (c5)10 and (c7)13. Then below I have a ration "effective tax rate" and to the right the inputted equation (=c5/c7). When I click show formula that is what is shown. I want it to show (=pretax income/tax paid).

I have tried changing to formula to equal the cells that have the text in them, instead of the values 10 and 10. I need this because I have about 100 ratios that I would like to see the actual inputs in them. Does this make sense?

I notice I can view what I need after that step by evaluating the formulas, I would rather have all the cells just show me the text in the equation at the same time, as I need to use them in another spreadsheet....

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Kyle Malm
  • 1
  • Parsing formula is very complex - I played briefly with a regexp to do this some years ago. Rob Van Gelder has a sample of a [formula tokenise here](http://www.dailydoseofexcel.com/archives/2009/12/05/formula-tokenizer/) this splits by formula but should be ready adaptable to split via value. I will take a look – brettdj Jan 06 '12 at 04:04

2 Answers2

1

You can achieve this using named ranges, but spaces are not allowed, so you'll have to use something like PretaxIncome or pretax_income instead of pretax income:

  • Select cell c5
  • click in the address bar, where it says C5, and type pretax_income
  • and so on.

I don't know of a way to get the formulas to change automatically, but once you've named your cells, you can reenter the formulas manually, so, for example, the effective tax rate formula would be =tax_paid/pretax_income

phoog
  • 42,068
  • 6
  • 79
  • 117
  • I was hoping to find a macro (which I'm not good with) basically I have it where I can use evaluate and just start doing it that way which maybe a little faster, and go through each one individually... I understand what you are saying, however that would mean I would have to redo the whole sheet, when I may as well start working on my new one in my other worksheet. If they just made an evaluate function... – Kyle Malm Jan 06 '12 at 00:52
0

If you need to use only pure Excel, you can't do it directly. Instead, your best option is to just have two independent cells, one that is =C5 & " / " & C7 to display the formula, and the other that is =C5 / C7 to evaluate the formula.

But if you want to evaluate the formula that is displayed (which is what I think you're going for), then you need to use a little VBA. It's easier than it sounds. Just create a new module (usually Alt + F11) and type in this function:

Function Eval(ByVal str As String) As String
  Eval = Evaluate(str)
End Function

Then on your spreadsheet, keep the same display cell (=C5 & " / " & C7) and then evaluate with just =Eval(D8), where D8 of course is your display cell location.

Steve Blackwell
  • 5,904
  • 32
  • 49