Q: Formula works in one row, but same formula (copied and pasted) will not work in other rows.

Question

Maddog_Walby

Level 1 (4 points)

iWork

Q: Formula works in one row, but same formula (copied and pasted) will not work in other rows.

I have a formula that, if there is a number in the box, multiplies it by a price, then sums the results in a final column. I copied it into 7 rows. It changed the row numbers as necessary, the formulas all look right, but nothing I seem to do make the other 6 work, unless I fill all the empty cells in the row with 0. Oddly, the one that does work is not the original row I typed the formula into, and it has empty cells.

Here is the formula, each column has a product, so the cell is multiplied by a number, that number being the cost of the product. If there is no product sold, the cell is empty, so it is 0.

IFERROR((B6×50)+(C6×30)+(D6×60)+(E6×40)+(F6×30)+(G6×25)+(H6×25)+(I6×20)+(J6×20)+ (K6×25)+(L6×20)+(M6×15)+(N6×10)+(O6×10)+(P6),0)

Ideas?

Thanks!

MacBook Pro, OS X Mountain Lion (10.8.4)

Posted on Sep 6, 2016 8:22 AM

I have this question too

Solved

Wayne Contello

Level 6 (18,960 points)

iWork

A:

Here is a way to do what you described:

I am assuming the constant multipliers are unchanging when this is used on different rows..

In this example, the multipliers are stored on the first row (but can be anywhere you like).

A2=SUMPRODUCT(B$1:P$1, B2:P2)

this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:

=SUMPRODUCT(B$1:P$1, B2:P2)

select cell A2, copy

select cells A2 thru the end of column A, paste

The constants are set up like this:

Here is the table already set up (you can copy and paste from here) then you need to just add the formula:

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

Posted on Sep 6, 2016 9:54 AM

Q: Formula works in one row, but same formula (copied and pasted) will not work in other rows.

All replies

Helpful answers

by t quinn,

t quinn Sep 6, 2016 8:59 AM in response to Maddog_Walby
Level 5 (4,930 points)

Mac OS X

Sep 6, 2016 8:59 AM in response to Maddog_Walby

Hi Maddog,

One of the difficulties with IFERROR() is that it makes it harder to debug formulas. I am not sure why you are using it here anyway.

If Wayne's suggestion doesn't solve it for you please post a screenshot. cmd-shift-4 will give you a cusror to take it the camera icon in the reply window will post it.

quinn
Reply options

Link to this post

by Maddog_Walby,

Maddog_Walby Sep 6, 2016 9:56 AM in response to Wayne Contello
Level 1 (4 points)

iWork

Sep 6, 2016 9:56 AM in response to Wayne Contello

Thank you. I had to look at it differently to use your suggestion. It adapted to my project perfectly!
Reply options

Link to this post

by Maddog_Walby,

Maddog_Walby Sep 6, 2016 9:58 AM in response to t quinn
Level 1 (4 points)

iWork

Sep 6, 2016 9:58 AM in response to t quinn

Thank you. The iferror was taken directly from the same project I had already done last year in Excel. I am learning how to do the things I know there in Numbers.
Reply options

Link to this post

Accepted Answer · Sep 6, 2016 9:54 AM

Wayne Contello

Level 6 (18,960 points)

iWork

Sep 6, 2016 9:54 AM in response to Maddog_Walby

Here is a way to do what you described:

I am assuming the constant multipliers are unchanging when this is used on different rows..

In this example, the multipliers are stored on the first row (but can be anywhere you like).

A2=SUMPRODUCT(B$1:P$1, B2:P2)

this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:

=SUMPRODUCT(B$1:P$1, B2:P2)

select cell A2, copy

select cells A2 thru the end of column A, paste

The constants are set up like this:

Here is the table already set up (you can copy and paste from here) then you need to just add the formula:

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

Q: Formula works in one row, but same formula (copied and pasted) will not work in other rows.

All replies Helpful answers

by Wayne Contello,Solvedanswer

by t quinn,

by Maddog_Walby,

by Maddog_Walby,

All replies

Helpful answers

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1

	50	30	60	40	30	25	25	20	20	25	20	15	10	10	1
381	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
50	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
60	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
40	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
30	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
25	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
25	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
15	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
50	1
60			1
30					1
20									1