help with excel formula

[danielson]

Hi guys, hoping someone can help with this..

running XP with Office 2003.

In this excel file i have in cell H2 the current euro value eg 1.45
All i want to do is enter in cells G8:G100 my euro cost prices and then have them converted into sterling in cells H8:H100
so my fomrula for H8 is "=G8/$H$2" , so if i need to change H2 at any time to new value then its auto reflected.
so all i did then was copy H8 right down to H100 and Paste Special - Formula..

the result is that if i look at each cell in turn eg H9,H10 , the formula is correct eg ="G9/$H2$" BUT! the actual cells are displaying the result of H8, ie i have
12.54 replicated all the way down to H100..?!?!

any ideas? its driving me nuts!

[fer]

danielson said:

Hi guys, hoping someone can help with this.. running XP with Office 2003. In this excel file i have in cell H2 the current euro value eg 1.45 All i want to do is enter in cells G8:G100 my euro cost prices and then have them converted into sterling in cells H8:H100 so my fomrula for H8 is "=G8/$H$2" , so if i need to change H2 at any time to new value then its auto reflected. so all i did then was copy H8 right down to H100 and Paste Special - Formula.. the result is that if i look at each cell in turn eg H9,H10 , the formula is correct eg ="G9/$H2$" BUT! the actual cells are displaying the result of H8, ie i have 12.54 replicated all the way down to H100..?!?! any ideas? its driving me nuts!

I normally just use cut and paste, without the paste special. If you highlight the cell you want, Ctrl-C, and then highlight the other cells and Ctrl-V that should do it.

HTH,
C

[danielson]

thanks for reply fer but thats not fixed it either..its put the correct formula into each cell, but its still displaying H2's formula answer ie 12.50 into each of these cells??!!

[fer]

Oops, sorry, cannot recreate it. If you want, you can try and email it to me for my inept evaluation.

[m-five]

Have you set your calaculation mode to manual?

[rsvmilly]

Just a shot in the dark.

Are you certain that the formula reads '=G8/$H$2' and not '=$G$8/$H$2' because that would explain it?

[atom290]

Check to see if there is the world calculate on the status bar? If it is then your calculation is set to Manual. You can do an automatic update within tools options, or press F9

Either that or have you set the formula to be
=a1/$b$1
or ="a1/$b$1"

if none of these work then you could mail me your excel spreadsheet example and I will mail you back my thoughts

[Jay-Aim]

danielson said:

thanks for reply fer but thats not fixed it either..its put the correct formula into each cell, but its still displaying H2's formula answer ie 12.50 into each of these cells??!!

you said H2 was 1.45 ie the exchange rate!

ditto here if you want to e-mail it to me

[danielson]

guys, thanks for this...just come back from lunch and it would appear i have my Calculate set to manual and the F9 option has worked a treat...

[atom290]

The calculation property is a bit of a bizzare one!

If you alter the property within Tools/options, it doesnt just change it for that workbook, it changes it for all workbooks.

If you then save one of the workbooks, it saves the calculation property with it.

On re-opening the workbook, any other workbooks will also take on the same atribute. and so the rot sets in!

Two extra points:

1. If excel thinks that the calculations are taking too long, vlookups are a major problem, then it will change the property to manual

2. If you dont think that F9 has worked then

Press F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.