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

Question

Level 1

9 points

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

Reply

Answer 1

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

Reply

Answer 2

t quinn

Level 5

7,063 points

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

Answer 3

Maddog_Walby Author

Level 1

9 points

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

Answer 4

Maddog_Walby Author

Level 1

9 points

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

	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