Issue with cell ranges and formulas

[John Eacrett]

I've got an interesting issue that I'd like some help
with. I'm doing a performance analysis where I'm trying
to average some numbers. Here's what I did to create the
worksheet:

In cell A1 enter 'Time', in B1 enter 'CPU #0', in C1
enter 'CPU #1', in D1 enter 'CPU #2', in E1 enter 'CPU
#3', and in F1 enter 'Average'.

I then format the cells in column A to use the 'time'
category and '1:30 PM' type.

Now I enter in cell A2 '8:30 PM' and A3 with '8:35 PM'.

Next enter '17.368' in cell B2, '11.253' in C2, '29.715'
in D2, and '49.368' in E2.

Next enter '78.451' in cell B3, '75.638' in C3, '14.587'
in D3, and '85.639' in E3.

What I want is an average of all the CPU's for each time
period, so in cell F2 I enter '=AVERAGE(B2:E2)' and get a
value of '26.926' and also I get an error "The formula in
this cell refers to a range that has additional numbers
adjacent to it".

To begin with, the error is bogus because the program
should be smart enough to figure out that you wouldn't
want to average values of different types (times and
regular numbers), but I tell excel to ignore the error and
I continue on.

Here's where it gets real interesting. I highlight cell
F2 and hit CTRL-C to copy the cell. I then right click in
cell F3 and click 'Paste Special', select 'formulas' and
click 'OK'. This should have copied the formula and
calculated the average for cells B3 through E3 and placed
the value in F3, instead I get a value of '26.926' and the
previously ignored error is back as well. This value is
clearly wrong as it should be '63.57875'. I even checked
the copied formula and it correctly shows the formula
as '=AVERAGE(B3:E3)'. The error is aggravating enough,
but the fact it doesn't calculate a correct value on
pasted formulas unless I double click the cell and hit
enter is making this very tedious. Does anyone have any
idea what is going on?

 

[kkknie]

First off, go to Tools | Options | Error Checking tab and get rid o
your error permanantly. I don't have any check on my machine sinc
they all seem a bit stupid.

As for the second question, you should just be able to cut and past
without paste special to copy the formula. Or just drag the botto
right corner down to copy. In any case, you need to go to Tool
Options | Calculation and set calculation to automatic (your i
probably set to manual).

 

[John Eacrett]

First off, go to Tools | Options | Error Checking tab and get rid of
your error permanantly. I don't have any check on my machine since
they all seem a bit stupid.

As for the second question, you should just be able to cut and paste
without paste special to copy the formula. Or just drag the bottom
right corner down to copy. In any case, you need to go to Tool |
Options | Calculation and set calculation to automatic (your is
probably set to manual).

K

Your suggestion stopped the error from being displayed, but the copied
formula still incorrectly calculates the value on the next row
displaying the same value in the original row. Could this be a bug?

John

 

[kkknie]

The only way I can duplicate your error is to set calculation to manual
Did you set it to Automatic using the instruction in my previous post?
If so, I'm at a loss to explain it.