BS: pasting formula in MS Excel

Subject: BS: pasting formula in MS Excel
From: GUEST,Dazbo at Work
Date: 02 Jun 08 - 09:41 AM

A problem I've just encountered in excel.

In A1 I have a constant, in this case 440.

I write a formula in A2 [A1*1.05] which Excel displays as 462.

I copy this formula and paste into A3. The formula bar shows the formula as A2*1.05 but the cell shows 462. I've not used special paste commands so it's not pasting in the value from A2.

I subsequently paste this formula down column A but each cell displays 462.

If I rewrite the formula into A3 Excel displays the correct answer.


I'm totally baffled, any idea what's going on?

(I've discovered the evaluate formula tool and it shows the correct formual, picks the value in A2 but the evaluate step displays it as if the value only is pasted).

Subject: RE: BS: pasting formula in MS Excel
From: Amos
Date: 02 Jun 08 - 09:45 AM

Reboot or restart Excel. What you are doing should produce the right results. If that fails you may want to consider reinstalling the application. It ain't behaving right. The formula, from what you say, reads the same in the cell whether you hand-type it or paste it, but if you paste it it counts the old values instead of the new relative ones.

You could try working around by going to the Paste Special menu and choosing formulas, but that's what it is supposed to be doing by default anyway.


A

Subject: RE: BS: pasting formula in MS Excel
From: GUEST,Dazbo at work
Date: 02 Jun 08 - 09:50 AM

Thanks Amos. I think I've sorted it - somehow, and it wasn't me chosing it, Excel switched to manual calculation rather than automatic. I changed the option back to automatic and it works now. Still baffled though.

Subject: RE: BS: pasting formula in MS Excel
From: GUEST,TIA
Date: 02 Jun 08 - 09:52 AM

Try this, and tell us what you see...
Click on a cell where the formula has been pasted (e.g. A4), then go to the formula bar and click on the formula. Which cell is highlighted (by a colored box) down in the spreadsheet? It should be A3.

Subject: RE: BS: pasting formula in MS Excel
From: GUEST,TIA
Date: 02 Jun 08 - 09:52 AM

Ooops, cross-posted. Sounds like you're good.

Subject: RE: BS: pasting formula in MS Excel
From: GUEST,Dazbo at working
Date: 02 Jun 08 - 10:25 AM

Thanks anyway Tia.

Subject: RE: BS: pasting formula in MS Excel
From: JohnInKansas
Date: 02 Jun 08 - 10:54 AM

Copy and paste in Excel does have some "peculiarities."

In at least some older versions, if you use Edit|Copy you can then use Edit|Paste Special, and can choose whether to paste values, formulas, Paste All (values and formulas), or a couple of other "selective forms."

If you use the shortcut "Ctl-C" to copy, the "Paste Special" choices may not be available. Using the shortcut "Ctl-V" to paste, in at least one older version I've used recently, nearly always pastes Values.

You can "copy down" by selecting a cell and dragging it down through a range of other cells, and you'll copy Values or "incrmented values" - unless you click on the lower right corner of the cell to be copied - where the cursor changes to a +. If you drag the "+" down it will copy "formulas" or "formulas incremented" depending on the initial selection. (That's a sort of vague description, but the best I can think of to describe what happens.)

You can also double-click on the "plus point" of a cell to copy formulas down to match the length of the left-adjacent column.

All probably not particularly relevant to the original question, as you appear to have described what you're doing to your own satisfaction; but not everyone else may be aware of the the variations possible.

John

Subject: RE: BS: pasting formula in MS Excel
From: Bonzo3legs
Date: 02 Jun 08 - 05:28 PM

What do you know about sumproduct?

Subject: RE: BS: pasting formula in MS Excel
From: Mr Red
Date: 02 Jun 08 - 05:34 PM

I was about to say manual/auto recalc.
another one is Text v general formtting, but the cell would show the formula rather than calculate - but it can be disconcerting if you haven't sussed it.

FWIW if you want to leave formatting as general, you can precede text with a single quote "'" and it will treat it as text which is handy for arithmetic operators that you want to show as text - like the dash.