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.

✔ 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 withnothingbetween 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.

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 toAddat the bottom of the dialog boxThese 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.

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

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.

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.

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 withnothingbetween 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.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History