No longer Excel, but an Access problem...exporting to Excel saving as "TEXT" type of data :(

[Helpless]

...I can't seem to get my autosum or other formulas to work right; I think it may be because some of the cells have NA (not available) in them instead of numbers...I thought excel would just skip over them, but if I change a 'NA' to a '0' it changes the sum ...anyway to filter these cells out? Or is there another forumla I can use to add them up that will just let NA=0? :disgust:

 

[OhioDude]

You can use the ISERROR worksheet function along with the IF conditional in each cell in the range you are summing to filter errors. Like this:

=IF(ISERROR({whatever}),0,{whatever})

If {whatever} returns an error value (#N/A?, #NAME?, etc.), then the cell will contain zero, otherwise the cell will contain the result of {whatever}.

Just for the sake of (a bad) example, lets say that A7 is the sum of rows 1 through 6 and A1 contains a reference to a named range called "MyRange" and MyRange contains the #N/A? error value, if I have the following formula in A1, my sum in A7 will still be correct:

=IF(ISERROR(MyRange),0,MyRange)

 

[Helpless]

...not really getting an error, just a value of $0.00, when I know the value should be in quite a few billion.

 

[OhioDude]

Each cell in the range you are summing needs to test for an error value in that particular cell.

A1 =IF(ISERROR({whatever}),0,{whatever})
A2 =IF(ISERROR({whatever}),0,{whatever})
A3 =IF(ISERROR({whatever}),0,{whatever})
A4 =IF(ISERROR({whatever}),0,{whatever})
A5 =IF(ISERROR({whatever}),0,{whatever})
A6 =IF(ISERROR({whatever}),0,{whatever})
A7 =SUM(A1:A6)

Any cell in the range A1:A6 that returns an error, will display a zero and not affect the sum in A7.

Perhaps I'm not following your question...

 

[Helpless]

hmmm, some of my sheets have 35K+ cells....It seems like Excel has always just skipped over any values that were not numerical and gave me a grand total on the bottom using the autosum feature...or a simple =SUM(A1:A35000) type of formula...I'll keep playing with it, but can't possible add that check to each and every cell...I must be overlooking something.

 

[Helpless]

Yea, I just did a simple test with a few cells--1,2,3,NA,4,5,NA,6,etc...and the autosum worked as it is supposed to....just not when I have a *bazillion* lines of data or extremely large numbers...just can't figure out how it returns an autosum of zero

 

[OhioDude]

I believe Excel used to allow you to filter out error values right in the cell formatting. As you know, formats are specified as follows, separated by semi-colons:

positive;negative;zero;text

If I remember correctly, it used to be:

positive;negative;zero;text;error

Another useful feature stripped out so that we could have great features like that cute little paper-clip. :|

 

[OhioDude]

Also, error values are treated differently than text. Excel handles throwing out text in summed ranges just fine, but not errors like #VALUE!, #N/A, #NAME?, etc.

 

[Helpless]

I know why it's not working now, but not how to fix it...seems that the numbers are actually being recognized as text, not numbers...maybe when I converted my access databases, I messed something up....all the numbers are text, or something like that (what a co-worker told me) I guess I'll check the Access help files for some solutions

 

[OhioDude]

Geez -- If I could see your workbook, I could probably give you a hand... It's difficult trying to describe with words what's going on in an Excel workbook to someone else...

 

[Helpless]

...sent you a PM, OhioDude...if you don't mind taking a peak at a sample of the data, just shoot me your email...

 

[RayH]

If you're using the import wizard in Excel, you can manually specify the data type for each of the columns you're importing.

 

[Helpless]

hmm, I'll try that...was just using Access to seperate the data, then just exported it back to an excel file from within Access...at this point, I'll try anything

 

[Helpless]

It's an Access issue I took the aggregate data I extract and performs the *autosum* perfectly...anything that went into Access and back out, come out as text, not numbers...I went into Access and created a new database, used the import wizard...as I was got to the second screen, I saw a box with Data type: TEXT but no way to change the option...so I am guessing I saved all these data in text format...but can't find where to change them back to the original format...I read that I should check "Save Formatted" when going back to excel and it will maintain the integrity of the data, but...ugh....they were wrong

 

[OhioDude]

Just sent you an email. This should get you fixed up.

 

[Helpless]

Thanks for the module, OhioDude...worked great! I did, however, see where I went wrong; the default in Access is text for data type, although most of the cells have data...If I click on the table, click on design view, it lists all the fields/rows I have and the data type...so just have to toggle 'number' for the type of data...then just save it as an excel file again.


It seems to me that with these different types of data, excel would have a feature to convert text to number format...since it does, in fact, have one to convert number to text....bastages


Again, thanks!!!

 

[Helpless]

...and thanks to Ray, too I didn't find that in excel, but I see exactly what you are talking about in Access.

 

[OhioDude]

Well, the thing about being able to convert from numeric to text is that any number can be displayed as text, but the opposite is certainly not true...

Just sent you another email. Let me know if you need any more info...