Keep the formula, show results in new cell

[HerrMetik]

Hello, Excel Wizards!

I help a schoolgirl with her math homework, and I would like to give her some simple excecises to solve in her head. So, here is the thing: I use Office 2003, and I would like to write a lot of simple calculations in the first column (6+5, 4*3, 8/2), and the solutions to be displayed right next to them in the next column - the original task shall remain visible, and the results be displayed in a different cell. How can I achieve that?

[MarvinP]

Hi HerrMetik,

If the first character in a cell is the equal sign, Excel will calculate it. If you don't start with an equal sign, Excel won't calculate.

So in A1 put 6 + 5
and in B1 put = 6 + 5 which will show as 11 (I think).

Hope that helps.

[gary28]

Marvin's solution will work for addition and most other math operations, but if you enter 8/2, Excel will throw you a curve by displaying a date, which will look something like 2-Aug (depending on your locale and date/time settings). Surprise!

It would be preferable to format the column in which you want to show the construct of the formulas as the Text data type. Doing so disables formulas by converting them to text, as well as converting any other data likewise. And this way you get to show the formula with the equal sign intact.

If you ever have any troubles with your formulas, this topic on Office.com should help you: Why is my formula broken?

[MarvinP]

Gary is Correct that 8/4 will be thought of as a date. To fix this start with a single quote character ' (whatever that is called - apostrophy?) That will force the stuff entered to be text.

I just thought it was a great idea getting students into Excel as they were learning the addition tables.

I did like the link from microsoft talking about formulas being broken. That is always good information to review.

[martindwilson]

select b1 then create a named formula insert/name define pick a name i just used doit ("do it")
in refers to put
=EVALUATE(Sheet1!A1) click ok
now in b1 type =doit
format column a as text
in a1 put 1+1
b1 will show 2 to get rid of value errors when cell is empty wrap it in an if
=if(a1="","",doit)

[royUK]

You could place the answers on a hidden sheet, then in a column formatted as marlett use this formula to mark the answers on the questions page

=IF(C3=Sheet2!B3,"a","")

[HerrMetik]

Hi HerrMetik,

If the first character in a cell is the equal sign, Excel will calculate it. If you don't start with an equal sign, Excel won't calculate.

So in A1 put 6 + 5
and in B1 put = 6 + 5 which will show as 11 (I think).

Hope that helps.

Hi Marvin,

thanks for the reply, but that is just a bit too simple for what I want to achieve. I want Excel to spare me work, not doublinge it by retyping every calculation (or pasting it with an added = in front).

Marvin's solution will work for addition and most other math operations, but if you enter 8/2, Excel will throw you a curve by displaying a date, which will look something like 2-Aug (depending on your locale and date/time settings). Surprise!

It would be preferable to format the column in which you want to show the construct of the formulas as the Text data type. Doing so disables formulas by converting them to text, as well as converting any other data likewise. And this way you get to show the formula with the equal sign intact.

If you ever have any troubles with your formulas, this topic on Office.com should help you: Why is my formula broken?

Ah, thank you gary, it is a good idea to format the first column that ways. Thanks!

select b1 then create a named formula insert/name define pick a name i just used doit ("do it")
in refers to put
=EVALUATE(Sheet1!A1) click ok
now in b1 type =doit
format column a as text
in a1 put 1+1
b1 will show 2 to get rid of value errors when cell is empty wrap it in an if
=if(a1="","",doit)

You, Sir, are now officially my hero. This is exactly what I had in mind. :D

I am encountering a problem, though. If I try to create a named fromla using tha exact code, I get an error message calling my formula invalid. If I download and open your provided beautiful example sheet, I get an error reading "This workbook contains one or more Microsoft Excel 4.0 macros. these macros may contain virses or other harmful code. These macros will be disabled". So... what is the isse here? Version differences? Mind you, until I click "OK" on the message window, the sheet is displayed in the backroung with all the example cells working. As soon as I click OK, the result-cells display the error #NAME?

So... how can I re-enable those macros?

[HerrMetik]

Yaa-haw. I jst found the Macro Security Setup myself. Thank you so very much, guys!