Looks like no one’s replied in a while. To start the conversation again, simply ask a new 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

27 replies

Badunit

Level 7

26,032 points

Aug 16, 2014 6:05 PM in response to Agosto Mortal

When pasted in "numbers" justify to the left, that is a strong sign that they are text, not numbers. Text justifies to the left, numbers justify to the right.

Aug 16, 2014 6:43 PM in response to Agosto Mortal

Agosto Mortal wrote:

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

Agnosto,

What I mean is that one possibility is that there is an error in how you modified the equations. Without seeing what you did, it's hard to say. It's clear by now that your percentages weren't being read as numeric values, but that may not be the only problem. The toughest problems often involve multiple issues.

I wonder if you inadvertently pasted over equations, effectively erasing them.

We're here to help and we want you to succeed. The more you share with us, the better our chances are.

Jerry

Yellowbox

Level 7

20,383 points

Aug 17, 2014 4:48 AM in response to Agosto Mortal

Hi Agosto,

The Grade Book (and any other template) has some cells where you can enter (or paste) values.

Other cells contain formulas that calculate results.

If you type or paste into cells containing formulas, the formulas will be replaced and will no longer work.

Other cells that depend upon those formulas will no longer work as the template intended.

Here is a way to see what a cell contains.

Click on a cell and look at the display at the lower left of the Numbers window. For example, in the Class Overview Table, Cell B4 shows that Arthur Albert scored 74% in Assignment 1. That is an actual value that was typed or pasted in. You can replace that value with another.

Columns G and H contain formulas. For example, cell G4 displays the result of 77% for Arthur Albert calculated by this formula:

=IF((COUNTA(A4)>0),ROUND(SUMPRODUCT(B4:F4,$B$3:$F$3),2),"--")

A complex formula that need not concern you (and I don't understand it!), but it is a formula that should not be overwritten.

Cell B19 contains a formula to calculate the Class Average. Similar for other cells in Row 19.

I agree that a template should just "do it" for you without your understanding the formulas. But work with the template, don't try to fight it.

To see the structure of a table, click anywhere in that table and Menu > Table > Header Rows (or Header Columns or Footer Rows).

For example, the Class Overview Table has three Header Rows:

And one Header Column and one Footer Row!

Numbers has Header Rows, Header Columns and Footer Rows that have magical properties 😉.

Excel does not.

Regards,

Ian.

Yellowbox

Level 7

20,383 points

Aug 17, 2014 5:24 AM in response to Agosto Mortal

Hi Agosta,

My screen shot was from the Grade Book template in Numbers 3.2. I believe that the same will apply to the Grade Book template in Numbers '09 (Numbers 2.3).

Another thought: before you paste data into the template, click in Cell B4 (the first Body Cell) then paste.

Regards,

Ian.

Aug 19, 2014 9:57 AM in response to Yellowbox

Thanks for your informative post, I know already what you mentioned.

I discovered (and solved thanks to help from here) how to add data to a sheet without screwing the formulas.

Now, as I posted before

HOW DO I MODIFY THE GRADE BOOK TEMPLATE TO MEET MY NEEDS?

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

INTO THIS: (my own sheet modified from the template: 8 weighted parts (columns) that add to a whole and final grade)

Yellowbox

Level 7

20,383 points

Aug 19, 2014 10:50 AM in response to Agosto Mortal

Hi Agosto,

Numbers 2.3.

From the Grade Book Template,

Cell G4 (Arthur Albert) contains this formula

=ROUND(SUMPRODUCT(B4:F4,$B$3:$F$3),2)

What is your formula in Cell J4 (Vladimir Putin)?

You have inserted more columns.

The solution could be to change $B$3:$F$3 to B$3:F$3

(remove the $ symbols that lock the formula to columns B and F).

Try this formula in J4 and Fill Down

=ROUND(SUMPRODUCT(B4:J4,B$3:J$3),2)

To help us help you, please reply with your formula in Cell J4 (Vladimir Putin's Total)

Regards,

Ian

Aug 19, 2014 11:47 AM in response to Yellowbox

HI,

there is nothing on J4 (Valdimir Putin's Total), when I did that I did not know about the formulas.

The "look" of that spreadsheet is what I would like to keep, I can either start adding the right formulas to the appropriate cells on my sheet or start from scratch modifying (again) the gradebook template. Still a few cells have formulas:

J3 has =SUM(B$3:I$3)

K4 has =IF(Total Vladimir Putin<0.6,Grade Scale :: $C$13,VLOOKUP(Total Vladimir Putin,$B:$Grade,2))

K5 has =IF(Total George Bush<0.6,Grade Scale :: $C$13,VLOOKUP(Total George Bush,$B:$Grade,2))

K6 has =IF(Total Pope Francis<0.6,Grade Scale :: $C$13,VLOOKUP(Total Pope Francis,$B:$Grade,2))

Class average and lowest and highest at the bottom have working formulas: