|
02 Jun 08 - 09:41 AM (#2355112) Subject: BS: pasting formula in MS Excel From: GUEST,Dazbo at Work 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). |
|
02 Jun 08 - 09:45 AM (#2355118) Subject: RE: BS: pasting formula in MS Excel From: Amos 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 |
|
02 Jun 08 - 09:50 AM (#2355124) Subject: RE: BS: pasting formula in MS Excel From: GUEST,Dazbo at work 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. |
|
02 Jun 08 - 09:52 AM (#2355125) Subject: RE: BS: pasting formula in MS Excel From: GUEST,TIA 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. |
|
02 Jun 08 - 09:52 AM (#2355127) Subject: RE: BS: pasting formula in MS Excel From: GUEST,TIA Ooops, cross-posted. Sounds like you're good. |
|
02 Jun 08 - 10:25 AM (#2355148) Subject: RE: BS: pasting formula in MS Excel From: GUEST,Dazbo at working Thanks anyway Tia. |
|
02 Jun 08 - 10:54 AM (#2355179) Subject: RE: BS: pasting formula in MS Excel From: JohnInKansas 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 |
|
02 Jun 08 - 05:28 PM (#2355552) Subject: RE: BS: pasting formula in MS Excel From: Bonzo3legs What do you know about sumproduct? |
|
02 Jun 08 - 05:34 PM (#2355562) Subject: RE: BS: pasting formula in MS Excel From: Mr Red 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. |