formulas

Question

In excel, autosum and other formulas are not working.

There have been no changes to my program that I know of.

When exporting a file to excel, then attempting to total the columns, I get "0" as the total each time.

Does anyone have any ideas of what may have occurred and how to fix this?

Answer 1

Glad that sorted things for you. I have no idea why Excel behaves in this way, I too have experienced the same issue but have never found a definitive answer.

One day you'll find that the method I gave you doesn't work because as well as being text values there are also invisible characters; usually char 160,  that stop this method converting them. I became so fed up with solving this on an ad hoc basis I now keep the macro below in my personal workbook to clean data before converting.

Sub CleanMe()

Dim c As Range

    For Each c In Selection

    If Not c.HasFormula Then

    With c

        .Value = WorksheetFunction.Trim(.Value)

        .Value = Replace(.Value, Chr(160), "")

    End With

    End If

    Next

End Sub

Answer 2

Hi,

I think the key here is when you say 'exporting' to Excel. Often these import values come into Excel as text values and need to be converted to numbers. Try this:-

Format the 'numbers' to the format you want but note changing the format does not convert them.

Put a 1 in an empty cell, select that cell and right click and copy.

Select your misbehaving numbers and right click and 'paste-special'. Select 'Multiply' and OK and then try your formulas again.

Answer 3

Thank you.

That fixed the issue.

I have exported these same report tables previously with no issue. Any idea why it has now decided not to recognize them as numbers?

Hi,

I think the key here is when you say 'exporting' to Excel. Often these import values come into Excel as text values and need to be converted to numbers. Try this:-

Format the 'numbers' to the format you want but note changing the format does not convert them.

Put a 1 in an empty cell, select that cell and right click and copy.

Select your misbehaving numbers and right click and 'paste-special'. Select 'Multiply' and OK and then try your formulas again.