problem- copying formula to other cells works fine bar 4 cells which return #VALUE!- help?

[BSWJON]

as the title suggests, I'm having a problem with copying a formula across multiple cells. Copying has worked fine on most of the cells however not for 4 cells which instead get #value! returned. It also says that data entered is of the wrong type.

I have checked and re-checked my data and formulas but have not come up with anything.

If anyone knwos what the problem could be, or how to fix it, I'd appreciate it.

The formula: =(SUMIFS(mahildsDATA!$AC$2:$AC$814,mahildsDATA!$M$2:$M$814,$AE39)/SUMIFS(mahildsDATA!$O$2:$O$814,mahildsDATA!$M$2:$M$814,$AE39))*100

This is how it looks (I would have put in a screenshot but can't figure out how to upload a pic...)

	Average of moisture fail %	Average of curve fail %	Average of c16 fail	Average of op fail % visual
length 2400	7.37	2.09	0.52	4.69
length 3000	10.70	3.97	0.25	3.58
length 3600	#VALUE!	#VALUE!	#VALUE!	#VALUE!
length 4000	2.61	3.98	0.00	2.23
length 4200	4.99	4.19	0.37	4.75
length 4800	4.79	3.73	0.51	3.30

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[BSWJON]

Anyone have any ideas? This is happening in pretty much every table that I am doing of this type, most cells work bar one category.

 

[Peter_SSs]

Welcome to the MrExcel board!

Could it be that mahildsDATA!$AC$2:$AC$814 or mahildsDATA!$O$2:$O$814 already contain some #VALUE errors?

To post small screen shots, see my signature block below.

 

[BSWJON]

The data (and there's a lot of it) doesn't have any #VALUE errors in it, some blanks but they haven't affected the cells that the formula does work in.

Here's an attempt at a screenshot of (some of) the dataset:

ht	width	length	section	Piece Volume	vol (m3)	av mc%	std dev	Grading Standard	total waste %	ms % (c16 fail)	curve fail %	moisture fail %	visual	op fail % visual	demension failure %	c16 pass	cycle type / notes	Column2	vol of moist fail	vol of curve fail2	vol of ms fail3	vol of op fail4
28	125	4800	28x125x4800	1680	0.29	29.2	1.1	c7	11.8		11.8		11.8						0.0	0.0	0.0	0.0
28	125	4800	28x125x4800	1680	144.76	29.2	1.1	c7	11.8		11.8		11.8						0.0	17.0	0.0	0.0
28	125	4800	28x125x4800	1680	1.34	21.7	3.9	c7	7.5		2.5		7.5	5.0					0.0	0.0	0.0	0.1
			47x150x4.8	0	176.81	21.1	2.6	c16	8.5	0.6	2.6	0.2		4.4		92			0.3	4.5	1.1	7.8
			47x175x4.8	0	108.57	21.1	2.6	c16	5.7	0.3	2.6	0.0		1.9		95			0.0	2.8	0.3	2.1
			47x225x4.8	0	44.26	21.0	2.8	c16	12.2	0.1	6.0	0.6		2.0		91			0.3	2.6	0.0	0.9
			47x250x4.8	0	83.93	22.5	3.2	c16	27.0	0.1	4.3	5.2		3.4		87			4.4	3.6	0.1	2.8
			41x94x4.8	0	50.3	21.9	4.7	clc16	31.1	1.1	8.5	15.0		4.2					7.5	4.3	0.6	2.1
			41x94x4.8	0	106.96	19.7	3.9	c16	18.7	1.1	5.3	6.5		5.3		82			7.0	5.7	1.1	5.7

<colgroup><col span="3"><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

As far as I can see the formulas are identical in each cell apart from the columns etc that need changing, so I can't see it being a problem with the formula, however there aren't any discrepancies that i can find within the data that are also affecting the cells associated with the formulas that do work.

 

[Peter_SSs]

The data (and there's a lot of it) doesn't have any #VALUE errors in it,

Let's just try to confirm that. In some vacant cells, what do these formulas return?

=SUMPRODUCT(--ISERROR(mahildsDATA!$AC$2:$AC$814))

=SUMPRODUCT(--ISERROR(mahildsDATA!$O$2:$O$814))

Repeat for any other columns that are being summed by a SUMIFS


Your sample data isn't much help since we don't know what the rows/columns are. Is it sheet 'mahildsDATA'?

 

[BSWJON]

yes thats correct about the sheet.

well, I it was your formulas that worked as I have now I identified a row of 4 cells returning #value, and they're attributed to one of the categories in the other table that returned #value as a result of my formulas. So what I'm saying is that the issue must be to do with my data, so thank you for helping me with that

I suppose now I need to find out what it is that is wrong specifically with the data.

The formula that is now returning #value is

Table1[[#This Row],[vol (m3)]]*(Table1[[#This Row],[moisture fail %]]/100)

I'm guessing that it is something to do with having text (or the cells formatted as text?) in the cells which have arithmetic functions asociated with them?

 

[BSWJON]

problem solved One cell messing so much up!

 

[Peter_SSs]

.. I have now I identified a row of 4 cells returning #value, ..

I thought there had to be some #VALUE errors in there somewhere.

problem solved

Great news!

 

[BSWJON]

Thanks for your help!