Microsoft Excel Formulas

[Chris]

I have exported financial (budget figures) data into excel for 12 months,
changed cells to numbers and added columns to put in actuals. I have manually
entered formulas in the total column from each cell (eg =sum(E2+E4+E6..) etc
but when I go back and modify data in the individual cells it does not change
the total figure. What is wrong??
Thanks

 

[ShaneDevenshire]

Hi,

First thing to make sure is that you calculation mode is set to automatic -
choose Tools, Options, Calculation tab and make sure Automatic is on.

Second, and not related to your problem, but if you want to sum every other
cell you should enter it as either:
=SUM(E2,E4,E6,E8)
or
=E2+E4+E6+E8

Third, you need to show us some sample data and the results you get before
you modify a number and after you modify the number. For example,
E
2 5
3
4 6
5
6 8
===
19

Fourth, if the cells are formatted as text than you may need to change the
format and reenter them as numbers. Numbers stored as text will not always
work correctly in calculations.


but when I replace 8 with 2 the result is still 19. Something like that.

 

[Max]

You could be facing 2 issues, text formatting (formulas placed in cells which
are inadvertently pre-formatted as text do not respond) and due to text
numbers imported (text numbers will not respond to formulas)

Try this:
Select the entire sheet with the imported data, format it as general (via
Format > Cells > General > OK). That "clears" it of all text formatted cells.
Then go to a new sheet (just to be sure), copy an empty cell. Then come back
to your sheet with the imported data, select the data range, right-click >
paste special > check "add" > ok. That should convert all text numbers at one
go to real numbers, w/o impacting the real numbers already there, if any.

Btw, your: =sum(E2+E4+E6..) should just be: =sum(E2,E4,E6..)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik