excel formulae
 

problem with entering new formulae. I want to sum cells w45 and x44, and
subtract cell x45. When I enter it the formulae looks right but the value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also = w45+x44-x45,
and other ways - no luck.

excel formulae
 

Sounds like the cells are formatted as text. Change that and it should be
okay.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jimi" wrote in message
...
problem with entering new formulae. I want to sum cells w45 and x44, and
subtract cell x45. When I enter it the formulae looks right but the value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also =
w45+x44-x45,
and other ways - no luck.

excel formulae
 

No need for sum AND addition operators at the same time

either

=SUM(W45,X44,X45)

or just

=W45+X44+X45

however as you have discovered it won't make a difference

make sure that you have calculation set to automatic under
toolsoptionscalculation

make sure the cells are formatted as general or number (NOT text)

Try to replace the equal sign with the equal sign to force calculation

Ctrl + h, find what = replace with =


Select the columns with the formulas and do datatext to columns and click
finish immediately



HTH

--
Regards,

Peo Sjoblom




"Jimi" wrote in message
...
problem with entering new formulae. I want to sum cells w45 and x44, and
subtract cell x45. When I enter it the formulae looks right but the value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also =
w45+x44-x45,
and other ways - no luck.

excel formulae
 

On Aug 2, 11:44 am, Jimi wrote:
problem with entering new formulae. I want to sum cells w45 and x44,
and subtract cell x45. When I enter it the formulae looks right but the
value comes up as 0 when it is not.
on another worksheet, this works. On this new one it doesnt, so
its like a format thing or whatever. I haev tried =sum(w45+x44-x45)
also = w45+x44-x45, and other ways - no luck.

First, stick with the latter form. In this context, SUM() is
superfluous.

Second, your formula looks essentially correct. The culprit probably
has to do with the contents of the cells themselves or with the cell
references. It is difficult to debug formula mistakes at arms-
length. Too much depends on your powers of observation and
communication. That's not a personal attack on you per se. We all
have this problem.

One thing that might help you to discover the problem on your own is
to use Tools Formula Auditing Evaluate Formula. It will show
you what values are being substituted as the formula is evaluated step-
by-step.

Some wild guesses....

1. Are W44, X44 and X45 blank on the worksheet that contains the above
formula?

2. Are you perhaps trying to refer to W44, X44 and X45 on __another__
work sheet? If so, you want to use the form Sheet1!W44, where
"Sheet1" is the name use in the workbook tab.

3. Does W44+X44-X45 evaluate to a value less than 1? If so, the 0
that you see might be the rounded value. Use Format Cells
Number to change the number of display decimal places. If you select
the Scientific format, you are assured of seeing the "significant"
digits, although you might not understand the form.

excel formulae
 

My guess is that the numbers are actually text.

Try this........................

Format all to General.

Copy an empty cell.

Select the range of data and EditPaste SpecialAddOKEsc


Gord Dibben MS Excel MVP

On Thu, 2 Aug 2007 11:44:04 -0700, Jimi wrote:

problem with entering new formulae. I want to sum cells w45 and x44, and
subtract cell x45. When I enter it the formulae looks right but the value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also = w45+x44-x45,
and other ways - no luck.

excel formulae
 

Thanks Bob, but thats not it.
Its strange. Played with the formatting in several ways, no luck.
It seems to happen anywhere on the spreadsheet - cant input new formulae!

"Bob Phillips" wrote:

Sounds like the cells are formatted as text. Change that and it should be
okay.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jimi" wrote in message
...
problem with entering new formulae. I want to sum cells w45 and x44, and
subtract cell x45. When I enter it the formulae looks right but the value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also =
w45+x44-x45,
and other ways - no luck.

excel formulae
 

If it was text formatting simply changing it back to number formatting won't
make a difference, you need to trigger a calculation to get it to work and
that's what I suggested in my answer to your post


--
Regards,

Peo Sjoblom



"Jimi" wrote in message
...

Thanks Bob, but thats not it.
Its strange. Played with the formatting in several ways, no luck.
It seems to happen anywhere on the spreadsheet - cant input new formulae!

"Bob Phillips" wrote:

Sounds like the cells are formatted as text. Change that and it should be
okay.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jimi" wrote in message
...
problem with entering new formulae. I want to sum cells w45 and x44,
and
subtract cell x45. When I enter it the formulae looks right but the
value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its
like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also =
w45+x44-x45,
and other ways - no luck.

excel formulae
 

If the numbers are text, it does no good to just re-format them to numbers.

You must get Excel to recognize them as numbers.

Format all to General.

Copy an empty cell.

Select the range of "numbers" and EditPaste SpecialAddOKEsc


Gord Dibben MS Excel MVP


On Thu, 2 Aug 2007 14:38:12 -0700, Jimi wrote:


Thanks Bob, but thats not it.
Its strange. Played with the formatting in several ways, no luck.
It seems to happen anywhere on the spreadsheet - cant input new formulae!

"Bob Phillips" wrote:

Sounds like the cells are formatted as text. Change that and it should be
okay.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jimi" wrote in message
...
problem with entering new formulae. I want to sum cells w45 and x44, and
subtract cell x45. When I enter it the formulae looks right but the value
comes up as 0 whenit is not.
on another worksheet, this works. On this new one it doesnt, so its like a
format thing or whatever. I haev tried =sum(w45+x44-x45) also =
w45+x44-x45,
and other ways - no luck.