excel formulae

[Guest]

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.

 

[Bob Phillips]

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)

 

[Peo Sjoblom]

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
tools>options>calculation

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 data>text to columns and click
finish immediately



HTH

 

[joeu2004]

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.

 

[Gord Dibben]

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 Edit>Paste Special>Add>OK>Esc


Gord Dibben MS Excel MVP

 

[Guest]

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!

 

[Peo Sjoblom]

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

 

[Gord Dibben]

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 Edit>Paste Special>Add>OK>Esc


Gord Dibben MS Excel MVP