not sure why this is happening> any thoughts?
not sure why this is happening> any thoughts?
Your percentages in column E are numbers stored as text. If you convert them to numbers, you will eliminate the divide by 0 error.
Edit: FYI, the same is true of columns F and G.
If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.
If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.
Hi,
Perhaps your data has been imported from some external source, without further modification?
In any case, the reason is that your entries in column E are not being recognised as numerics.
One quick way to correct this issue:
Highlight that column, go to Text to Columns (Data tab) and click Finish immediately (without making any prior selections).
Regards
The data in column E is numbers stored as text. Use Text to Columns to convert it to true numbers and it will be fine.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
The issue is with % numbers being Text. Convert them to numbers by highlighting and right clicking.
One other thing you shouldn't ever average a %
Sorry I am lazy so I did just copy paste this.
A common mistake I often observe in data analysis is the averaging of percentages. To explain this concept let’s examine a simple example.
Suppose we conduct a survey on whether people like or do not like chocolate. We discover that 90% of children like chocolate; however, only 60% of adults like chocolate. So, can we claim that 75% of the population ( average = (90%+60%)/2 = 75% ) like chocolate? The answer is NO! There is one exception which we will discuss at the end of this post.
The reason this is not correct is because we do not know anything about the sample size. Let’s provide more information. In the table below we learn that there are 100,000 children and 400,000 adults surveyed. From those, 170,000 people do not like chocolate, while 330,000 like chocolate. Here, we can confirm that 90,000/100,000x100% = 90% of the children, and 240,000/400,000x100% = 60% of adults like chocolate.
Right; but there are lots of circumstances where you can. Just to find a quick example so I don't have to make one up, from mathforum.org:
Percentages are just numbers and can be averaged like any other. Averaging may or may not be the solution method for a particular problem.For example, if there are 50 questions on an exam, and three students got 20%, 30%, and 40% of them right, then the average number of questions they got right is 30%, or 15 questions.
Thanks guys for all the help. Yes, I am exporting this from access into excel. How can I set the export so that the numbers come in as numbers and not text? Or do I just need to convert every time.
you either export from a query in access that converts the field to calculated values that are numeric (although why are they stored at text in the first place)
possibly look at the val function in access
or you write some code to convert them on opening excel, or do as the solutions above. the best solution is to export the data in the correct format
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks