I'm getting a #DIV/0! error even if cells have data

August 12, 2017 at 00:28:46
Specs: Windows 7
I generated a report with the scorecard rankings per month. I copied and pasted the data to a new workbook and I'm trying to get the average for three months. I have data in number format but whenever I'm using the average formula, it always results to a #DIV/0!. I don't know what's wrong. What I noticed is when I go the cells with data and re-type the data manually, the average formula works fine. Please help! I am willing to share my excel file if needed.

See More: Im getting a #DIV/0! error even if cells have data

Reply ↓  Report •

#1
August 12, 2017 at 05:13:11
The data that you are downloading may be ending up as text or may contain a hidden character. That is common with downloaded data.

Try this first: Select the data and try to change the format to Number.

If that doesn't work, try this:

Select an empty cell
Ctrl-C to Copy
Select the downloaded data
RIght-Click, Paste-Special
Click the bad next to Add at the bottom of the dialog box

These steps may convert the text to numbers because you have performed a mathematical operation on the data. You added 0 to the data. You could also multiply by 1, subtract 0, etc. Anything that doesn't actually change the value, just the format.

If that doesn't work, use the LEN function to see if it returns the same number as the number of characters that you see in the cells. Let us us know what happens.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
August 13, 2017 at 03:33:16
Thanks for your reply, DerbyDad03. I tried changing the format to number but it didn't work. I'm sorry but I'm confused with the second workaround whey you say "Click the bad next to Add at the bottom of the dialog box." When I right clicked and selected "Paste-Special," I see "Paste," "Paste Values" and "Other Paste Options." I tried the "LEN" function but I'm getting a different result. For example, the data in A1 is 4.47 but when I used LEN, it returned as 14. I have figured out this before but I forgot how to do it.

message edited by ChGatsby


Reply ↓  Report •

#3
August 13, 2017 at 11:39:40
re: "Click the bad next to Add at the bottom of the dialog box."

I meant to say "box", not "bad", but it's actually a radial button. Didn't you see the mathematical operators at the bottom of the Paste Special dialog box?

re: "the data in A1 is 4.47 but when I used LEN, it returned as 14. I have figured out this before but I forgot how to do it."

Obviously, there are characters in the cell other than the number you want.

You can try the CLEAN function...

https://support.office.com/en-us/ar...

...or the TRIM function:

https://support.office.com/en-US/ar...

You can even use both:

=TRIM(CLEAN(A1))

=CLEAN(TRIM(A1))

There are other ways to extract the number, but we would need to more information. Try the CLEAN and TRIM functions before we go any further.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •
Related Solutions


Ask Question