Solved 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 •

✔ Best Answer
August 20, 2017 at 05:33:37
Try the SUBSTITUTE function:

=SUBSTITUTE(A1," ", "")

Excel uses double quotes to designate Text. Therefore the above formula is telling Excel to substitute any space (the double quotes with the space between them) with nothing (the double quotes with nothing between them)

The value returned by the SUBSTITUTE function is going to be a Text string, so you should probably convert the result to a number by performing a mathematical operation on the result:

=SUBSTITUTE(A1," ", "")*1

The *1 isn't always needed since some Excel functions will use the result of the SUBSTITUTE function as a number, but to be safe, I would add it.

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



#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

#4
August 19, 2017 at 23:46:24
Thank you. The TRIM and CLEAN functions did not work. What I noticed In the cell is there are spaces after the actual value. For example, the data in A1 is 4.47. When I checked the bar where the data is entered (I don't know what it's called), it looks like 3.95 (10 spaces) or "3.95 l" The "l" stands for my cursor point. What I did is I deleted these spaces and the average formula works. The problem is I have like more than 111 cells. I tried the format painter and it didn't work.

Reply ↓  Report •

#5
August 20, 2017 at 05:33:37
✔ Best Answer
Try the SUBSTITUTE function:

=SUBSTITUTE(A1," ", "")

Excel uses double quotes to designate Text. Therefore the above formula is telling Excel to substitute any space (the double quotes with the space between them) with nothing (the double quotes with nothing between them)

The value returned by the SUBSTITUTE function is going to be a Text string, so you should probably convert the result to a number by performing a mathematical operation on the result:

=SUBSTITUTE(A1," ", "")*1

The *1 isn't always needed since some Excel functions will use the result of the SUBSTITUTE function as a number, but to be safe, I would add it.

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


Reply ↓  Report •

Ask Question