Problem Importing Excel Data into Numbers

Question

Problem Importing Excel Data into Numbers

Hi Everyone,

I am using Numbers to grade classes.

While trying to lear the program in order to create a grade book/attendance spreadsheet, I found the templates.

I am using a variation of the template "Grade Book".

I was able to taylor it to my particular need, which includes having weight course work so that each part is worth a different percentage.

The problem I have is that I need to import grades from an excel spreadsheet and when I copy them, includes the formulas that were in the template before. I tried paste, paste and match style and paste value.

e.g

and this is an example of the data I need to copy into the respective columns, in this case, ELEMENTS And MIDTERM, disregards the 2nd row, that contains the points, I need to copy and paste the percentages and have the spreadsheet calculate total and final grade.

iMac (27-inch, Late 2012), OS X Mavericks (10.9.1)

Posted on Aug 5, 2014 1:52 PM

Reply

Answer 1

Aug 13, 2014 1:07 PM in response to Agosto Mortal

ANYBODY?

Reply

Answer 2

Aug 13, 2014 1:21 PM in response to Agosto Mortal

Have you tried the following:

import excel file to Numbers

select the data you want to copy

copy

select the cell where you want to paste the data

select then menu item "Edit > Paste Formula Results"

Reply

Answer 3

Aug 13, 2014 1:36 PM in response to Wayne Contello

I don't have that option on EDIT.

The only options I have (under edit) are:

Paste

Paste and Match Style

Paste Values

I have Numbers version 2.3 (554)

Reply

Answer 4

Aug 13, 2014 1:44 PM in response to Agosto Mortal

Agosto,

Paste Values and Paste Formula Results are the same thing, with a different menu name. Paste Values is the option in Numbers V2 and Paste Formula Results is the option in Numbers V3. They do exactly the same thing.

It saves time and minimizes confusion if you mention the Numbers version in your question. It's possible that you believe you are using V3 but have launched V2 in inadvertently. It won't hurt to use version 2, but it's a good idea to be mindful of the version.

Jerry

Reply

Answer 5

Aug 13, 2014 1:45 PM in response to Agosto Mortal

try Paste Values

Reply

Answer 6

Aug 13, 2014 2:07 PM in response to Wayne Contello

I did, and it does some wierd things:

1 the calculations wrong:

EX:

CORRECT: (like the template above, where everything was entered by hand, or modified form the educational template)

Elements exam Final score

80% 60% D

After PASTE VALUE:

Elements exam Final score

100% 52% F THIS CALCULATION IS WRONG, getting a 100% on the particular exam should have boosted the final grade!

2. IT does not calculate Class average at the end of the column it shows a RED TRIANGLE WITH Exclamation mark inside the cell instead

Reply

Answer 7

Aug 13, 2014 2:54 PM in response to Agosto Mortal

For example, this is what I come up with after deleting all the data from the template:

The class average and lowest/highest works, but the Total score and grade (last 2 columns) does not. HOw can I add that functionality?

I though I was editing from the original template that has that formulas. How can I calculate my grades now?

Reply

Answer 8

Aug 13, 2014 8:35 PM in response to Agosto Mortal

Agosto,

We can't help you without seeing your expressions. You have modified a template that is a complicated one, so it would be difficult to guess at where the problem might be.

Jerry

Reply

Answer 9

Aug 13, 2014 8:50 PM in response to Jerrold Green1

HI Jerrold,

I don't know what you mean by: "We can't help you without seeing your expressions"

I did modified the template, all I wanted is add some more columns with different weight percentage. I realized now that the formulas are not there, the ones that calculate the final grade and letter. like in the template.

How can I fix this?
THanks

Reply

Answer 10

Aug 15, 2014 7:57 AM in response to Agosto Mortal

ANYBODY?

Reply

Answer 11

Badunit

Level 7

25,838 points

Aug 16, 2014 6:28 AM in response to Agosto Mortal

It is difficult to determine what you are doing and what is going wrong. I will assume when you copy/paste values from Excel into the Numbers spreadsheet that the data you pasted "looks" right. My guess is that it is being pasted in as text and treated as text, not as numbers or percentages. Different formulas treat text differently. For example, if the text in cell B2 is "100%" and you use the formula =B2 you will get 1 (or 100%) but if you use the formula =SUM(B2) you will get 0.

Some ideas (each is mutually exclusive):

Try Paste and Match Style instead of Paste Values
After pasting, change the cell formats of the pasted data to a number format or percentage
As a test, in two empty cells in your spreadsheet use formulas like those I gave above to test one of these improperly working pasted in percentages. Report back the results. As a second test, use the formula =LEN(B2) (where "B2" is one of your pasted in cells) and see if the length of the string is the same as what you see on the screen (100% should be 4 characters)

Reply

Answer 12

Aug 16, 2014 4:17 PM in response to Badunit

Hi Badunit,

Thanks a lot for trying to help, I am reporting back to you.

As you said it is difficult to determine what is wrong, but more difficult is try to use this program, a template should be EASY to use and modify! I strongly regret getting this app instead of the rival office!

Anyway: I don't know what you mean by:

"For example, if the text in cell B2 is "100%" and you use the formula =B2 you will get 1 (or 100%) but if you use the formula =SUM(B2) you will get 0."

I used the template with the intention to work, I have no idea what the formulas are in there (=B2, =SUMB2 etc..) nor how to use them. Thats the idea about the template right? that it works! right away, if not I should have started from scratch and invest time trying to learn the program and build my own spreadsheet, too time consuming and complicated anyway!!

I started with opening the grade book template, changing the format value on all the cells to percentage with 2 decimals (ej: 95.50%) to match the ones I have from the excel spreadsheet that I need to import. THEN Past and Match Style and it doesn't work!!!! So iF it is a working template provided by Apple, why is it that pasting (and match style) DOES NOT WORK ????

The Third suggestion (bullet point) I don't understand at all, what do you mean: "As a test, in two empty cells in your spreadsheet use formulas like those I gave above to test one of these improperly working pasted in percentages." ?????

HELP!!!!

Reply

Answer 13

Badunit

Level 7

25,838 points

Aug 16, 2014 4:44 PM in response to Agosto Mortal

Copying data from Excel and pasting it into Numbers can sometimes be a problem. What gets copied to the clipboard from Excel may not paste correctly in other apps, like Numbers.

I was suggesting that the pasted "percentages" are possibly being treated like text, not numbers. I don't know if that is the case or not and you are not familiar enough with Numbers or spreadsheets to find out. I pointed out that some formulas can work with text as if it was numbers but some functions will treat all text as non-numeric (i.e., ignore it completely or act like it is zero).

What someone else suggested earlier was to import the Excel spreadsheet (the one with the grades) into Numbers as a new document then copy the data from it. Whatever you copy from the Numbers version of the spreadsheet should paste correctly into your template, or at least be fixable. You can then discard (do not save) the Numbers version of the Excel file. This is a good suggestion and you should try it.

Reply

Answer 14

Aug 16, 2014 5:08 PM in response to Badunit

I understand that, it makes sense. I will try your suggestion.

Reply

Answer 15

Aug 16, 2014 5:40 PM in response to Badunit

Ok, Now that did something.

I was able to create a new document and copy the grades (data from the excel). The format (percentage with 2 decimals) was preserved (showed correctly) on the new blank document (but justified to the left) SEE PIC1 below. I went to the Inspector Cell for the whole column and it showed cell format: Automatic. I copy the column then past and match style, values were preserved on the grade template but it dod not work. It showed the same left justification of the data in the column as it did on the new document. NOW accidentally I went back to the new document and click again on the Inspector Cell, selected percentage and added 2 decimals (see PIC2) (that changed the justification to the right on the column) then copy, then paste and match a style and it worked (with justification on the right! on the grades sheet). So bingo, I was able to copy a whole column of data into the grade book template of numbers and the formulas worked. NOW I need to add columns with different weights on it:

GRADE BOOK TEMPLATE: 5 weighted parts (columns) that add to a whole and final grade

test1 test2 test3 test4 test5 Total Final Grade

15% 15% 20% 20% 30% 100% (letter A to F)

I Need: (look my first post) 8 or 9 columns with different weight,

So back to square one, HOW DO I MODIFY THE GRADE BOOK TEMPLATE TO MEET MY NEEDS?

Thanks a lot,

PIC 1: data pasted from excel file into NEW blank document ( left justification, value was preserved), paste and match style did not work

PIC2: after changing to Percentage and adding 2 decimals (now right justification, value was preserved) paste and match style DID work!

Reply