I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

Make sure that the content of C2 is a REAL number.
--
Gary''s Student - gsnu200828


"Aussie Rob" wrote:

I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

Perhaps the numbers in column C are text?

Format all to General or Number then copy an empty cell.

Select column C and Paste SpecialAddOK?Esc


Gord Dibben MS Excel MVP

On Wed, 21 Jan 2009 12:06:12 -0800, Aussie Rob
wrote:

I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

C2 is a real number although it is a copy and paste from a Visa statement.

"Gary''s Student" wrote:

Make sure that the content of C2 is a REAL number.
--
Gary''s Student - gsnu200828


"Aussie Rob" wrote:

I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

The column is a number. When I attempt the fix suggested it changes the
numeric value in the cell into "VALUE"

"Gord Dibben" wrote:

Perhaps the numbers in column C are text?

Format all to General or Number then copy an empty cell.

Select column C and Paste SpecialAddOK?Esc


Gord Dibben MS Excel MVP

On Wed, 21 Jan 2009 12:06:12 -0800, Aussie Rob
wrote:

I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

A few possible things could be happening.

If you have it in manual calculation mode, it obviously won't calculate
which could be caused by opening up a workbook that has manual calculation
set in it (if there is no other workbook already open when that one workbook
is opened)

If you purposely have it in manual calculation and press the F9 key, it
works for more simple calculations, but as calculations gets to be more
complex, it no longer works properly, so that's why I refuse to use the F9
function by itself within Excel. You can use Shift F9 to calculate just the
worksheet or Alt-Ctrl-F9 to force an entire calculation across all open
workbooks.


If you are putting in formulas and nothing is calculating properly, even
after pressing Alt-Ctrl-F9, then chances are, something may not be working
properly and generally when this happens, have to reboot the system.

The column where you putting the formula in may not be going all the way up
the column (if there are breaks in the column in between records), so be
careful with this aspect, especially if using something like subtotals.
When you first click on the cell, you can press Alt-= (That is to press and
hold the Alt key as you press the = [Equal] key), then within the range
highlighted, you may select the range or directly edit the range, which will
take you from Point mode to Edit mode of editing the formula in the cell.


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Aussie Rob" wrote in message
...
I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

Select column C and Paste SpecialAddOK?Esc

This should have read Select column C and Paste SpecialAddOKEsc

Just because they are formatted as Number does not mean they are numbers.

In a helper column enter =ISNUMBER(C2)

TRUE or FALSE?


Gord

On Wed, 21 Jan 2009 13:12:01 -0800, Aussie Rob <Aussie
wrote:

The column is a number. When I attempt the fix suggested it changes the
numeric value in the cell into "VALUE"

"Gord Dibben" wrote:

Perhaps the numbers in column C are text?

Format all to General or Number then copy an empty cell.

Select column C and Paste SpecialAddOK?Esc


Gord Dibben MS Excel MVP

On Wed, 21 Jan 2009 12:06:12 -0800, Aussie Rob
wrote:

I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

1 Shut down the computer and started again. No change to the problem.

Opened the file direct from "my documents menu" so no other file open.


Auto Calculation is on

Column goes all the way to Row 2 - Row 1 is the column Header "Restaurant"


"Ronald R. Dodge, Jr." wrote:

A few possible things could be happening.

If you have it in manual calculation mode, it obviously won't calculate
which could be caused by opening up a workbook that has manual calculation
set in it (if there is no other workbook already open when that one workbook
is opened)

If you purposely have it in manual calculation and press the F9 key, it
works for more simple calculations, but as calculations gets to be more
complex, it no longer works properly, so that's why I refuse to use the F9
function by itself within Excel. You can use Shift F9 to calculate just the
worksheet or Alt-Ctrl-F9 to force an entire calculation across all open
workbooks.


If you are putting in formulas and nothing is calculating properly, even
after pressing Alt-Ctrl-F9, then chances are, something may not be working
properly and generally when this happens, have to reboot the system.

The column where you putting the formula in may not be going all the way up
the column (if there are breaks in the column in between records), so be
careful with this aspect, especially if using something like subtotals.
When you first click on the cell, you can press Alt-= (That is to press and
hold the Alt key as you press the = [Equal] key), then within the range
highlighted, you may select the range or directly edit the range, which will
take you from Point mode to Edit mode of editing the formula in the cell.


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Aussie Rob" wrote in message
...
I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

There's one web site where I download the data from the website and it
doesn't covert the numbers properly either within Excel via the normal means
cause of the use of the internet space character (ASCII Code 160). The only
way to get it to convert properly is to use a formula similar to the one
below:

=VALUE(TRIM(SUBSTITUTE(D5,CHAR(160),"")))

This will convert any internet space (ASCII Code of 160) (this space is to
be of equal length as other characters rather than the shortened true font
space of ASCII Code 32. The only draw back is you will have to put this in
a different column from the column that has the downloaded data. However,
at least once you have the formula setup, it will be there ready for you to
use.

Give this a shot and reply back if this fixed your issue.
--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Aussie Rob" <Aussie wrote in message
...
C2 is a real number although it is a copy and paste from a Visa statement.

"Gary''s Student" wrote:

Make sure that the content of C2 is a REAL number.
--
Gary''s Student - gsnu200828


"Aussie Rob" wrote:

I am working on a financial spreadsheet and use the "IF" formula to
place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask
for
"autosum" for the resultant column it will not calculate. Any
suggestions?

It appears that the copy and paste method of transfer from my online visa
statement was causing the problem. If I enter the value manually then the
formula will work. If I copy and paste then it will not work.

"Aussie Rob" wrote:

1 Shut down the computer and started again. No change to the problem.

Opened the file direct from "my documents menu" so no other file open.


Auto Calculation is on

Column goes all the way to Row 2 - Row 1 is the column Header "Restaurant"


"Ronald R. Dodge, Jr." wrote:

A few possible things could be happening.

If you have it in manual calculation mode, it obviously won't calculate
which could be caused by opening up a workbook that has manual calculation
set in it (if there is no other workbook already open when that one workbook
is opened)

If you purposely have it in manual calculation and press the F9 key, it
works for more simple calculations, but as calculations gets to be more
complex, it no longer works properly, so that's why I refuse to use the F9
function by itself within Excel. You can use Shift F9 to calculate just the
worksheet or Alt-Ctrl-F9 to force an entire calculation across all open
workbooks.


If you are putting in formulas and nothing is calculating properly, even
after pressing Alt-Ctrl-F9, then chances are, something may not be working
properly and generally when this happens, have to reboot the system.

The column where you putting the formula in may not be going all the way up
the column (if there are breaks in the column in between records), so be
careful with this aspect, especially if using something like subtotals.
When you first click on the cell, you can press Alt-= (That is to press and
hold the Alt key as you press the = [Equal] key), then within the range
highlighted, you may select the range or directly edit the range, which will
take you from Point mode to Edit mode of editing the formula in the cell.


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Aussie Rob" wrote in message
...
I am working on a financial spreadsheet and use the "IF" formula to place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask for
"autosum" for the resultant column it will not calculate. Any suggestions?

If you are copying from a website (which I assume you are based on the
statement of you saying online visa statement), then you will need to
convert any spaces with the ASCII Code of "160" to the ASCII Code of "32" or
to convert as empty string, which ever is better suited to your situation.

Why do web sites use ASCII Code 160 instead of the standard ASCII Code of 32
that other software applications (I.e. Excel, Word, Access, Lotus Notes,
etc...) use?

Main reason, ASCII code 160 has a fixed length spacing for HTML coding while
ASCII code of 32 is not a fixed length. By using fixed length spaces, they
can have tables shown with the proper borders and what not. Imagine
attempting to doing ASCII art with true type fonts (New Courier) as opposed
to the old traditional fixed width character fonts (Courier). This is the
basic reason why web sites use ASCII code of 160 for spaces while other
applications use ASCII code of 32.

As for Excel formulas when attempting to use the ones that suppose to
convert text to numbers, they are not programmed to pick up the ASCII code
of 160 like they do with the ASCII code of 32.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Aussie Rob" wrote in message
...
It appears that the copy and paste method of transfer from my online visa
statement was causing the problem. If I enter the value manually then the
formula will work. If I copy and paste then it will not work.

"Aussie Rob" wrote:

1 Shut down the computer and started again. No change to the problem.

Opened the file direct from "my documents menu" so no other file open.


Auto Calculation is on

Column goes all the way to Row 2 - Row 1 is the column Header
"Restaurant"


"Ronald R. Dodge, Jr." wrote:

A few possible things could be happening.

If you have it in manual calculation mode, it obviously won't calculate
which could be caused by opening up a workbook that has manual
calculation
set in it (if there is no other workbook already open when that one
workbook
is opened)

If you purposely have it in manual calculation and press the F9 key, it
works for more simple calculations, but as calculations gets to be more
complex, it no longer works properly, so that's why I refuse to use the
F9
function by itself within Excel. You can use Shift F9 to calculate
just the
worksheet or Alt-Ctrl-F9 to force an entire calculation across all open
workbooks.


If you are putting in formulas and nothing is calculating properly,
even
after pressing Alt-Ctrl-F9, then chances are, something may not be
working
properly and generally when this happens, have to reboot the system.

The column where you putting the formula in may not be going all the
way up
the column (if there are breaks in the column in between records), so
be
careful with this aspect, especially if using something like subtotals.
When you first click on the cell, you can press Alt-= (That is to press
and
hold the Alt key as you press the = [Equal] key), then within the range
highlighted, you may select the range or directly edit the range, which
will
take you from Point mode to Edit mode of editing the formula in the
cell.


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Aussie Rob" wrote in message
...
I am working on a financial spreadsheet and use the "IF" formula to
place
information in a result column. =IF(F2="restaurant",C2,0)
This formula puts the value into the restaurant column but when I ask
for
"autosum" for the resultant column it will not calculate. Any
suggestions?