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...)

[TABLE="width: 730"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Average of moisture fail %[/TD]
[TD]Average of curve fail %[/TD]
[TD]Average of c16 fail[/TD]
[TD]Average of op fail % visual[/TD]
[/TR]
[TR]
[TD]length 2400[/TD]
[TD]7.37[/TD]
[TD]2.09[/TD]
[TD]0.52[/TD]
[TD]4.69[/TD]
[/TR]
[TR]
[TD]length 3000[/TD]
[TD]10.70[/TD]
[TD]3.97[/TD]
[TD]0.25[/TD]
[TD]3.58[/TD]
[/TR]
[TR]
[TD]length 3600[/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]length 4000[/TD]
[TD]2.61[/TD]
[TD]3.98[/TD]
[TD]0.00[/TD]
[TD]2.23[/TD]
[/TR]
[TR]
[TD]length 4200[/TD]
[TD]4.99[/TD]
[TD]4.19[/TD]
[TD]0.37[/TD]
[TD]4.75[/TD]
[/TR]
[TR]
[TD]length 4800[/TD]
[TD]4.79[/TD]
[TD]3.73[/TD]
[TD]0.51[/TD]
[TD]3.30[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

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

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!