Formulas Not Working on Imported Data

[Macro-wave]

I have a spreadsheet that is importing data. That part is working fine. I have added an addition column that calculates an average if there are numbers there to average. If there are no numbers there to average, then the cell is left blank.
The formula I am using is as follows:

Please Login or Register  to view this content.

This formula works for data that is already there, and I have copied this formula all the way to the very bottom of the spreadsheet, so even blank columns have the formula in them, presumably waiting to be filled as soon as there is data to average.

But it just doesn't do the calculation right. When new data is added, the formulas are there just like they always are, but they are taking numbers to be the equivalent to a blank.
I've even tried the following:

Please Login or Register  to view this content.

But that doesn't work either. When the new data comes in, the formula is there, and I can clearly see that there is an 82 or something there, which is obviously not equal to zero, but Excel is taking it to be a zero anyway.
I've worked with many types of problems out in Excel over the course of my career, but I have never dealt with sheets that are set up to automatically import data from... actually I don't know where. This was handed to me from someone else.

This is frightening. Any and all help with this situation would be greatly appreciated.

[Cutter]

Your imported data is text, even though it looks like numbers.

The AVERAGE() function ignores text so your formula returns a blank.

[JimmyA]

Hi Macro-wave

Could it be that the Sheet is set to Manual Calculation so the new data is not chaning the formula results?

If you look at the bottom left of your Excel Screen does it say "Calculate"?

Try adding this your code.....

Please Login or Register  to view this content.

Hope this helps

Cheers

Jim

[Macro-wave]

Your imported data is text, even though it looks like numbers.

The AVERAGE() function ignores text so your formula returns a blank.

Thanks. This seemed to be the problem. I formatted all the blank cells to be Numbers instead of General, and it seems to be working now.

Thanks!

[JimmyA]

Hi Again

Seeing Cutter's responce I think this formula would sort the problem.

=IF(A2="","",AVERAGE(TEXT(A1,0),TEXT(A2,0)))

Cheers

Jim