I'm attaching a cut down version of my file. On the ED Elec Report worksheet in Cols C15 down and F15 to J15 down every formula returns a #NUM error and I can't work out why. The formulas in row 14 are all ok. Any help would be appreciated. Thanks.
I'm attaching a cut down version of my file. On the ED Elec Report worksheet in Cols C15 down and F15 to J15 down every formula returns a #NUM error and I can't work out why. The formulas in row 14 are all ok. Any help would be appreciated. Thanks.
There are NO #NUM errors on the ED Elec Report from C15:J15 donwards.
If you're going to supply example data at least check it properly before posting.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
You might notice every formula begins with IFERROR and if you run a Evaluate Formula under the formula menu you will find a #NUM! error on every one.
If you filter out the unwanted data so you get left with Site 60
ANTHONY HUGHES is the only person who's relevant to that date range.
Where the data doesnt match the set conditions in your formula, ie
IF(('ED Elec Income'!$C$2:$C$50000=$H$6+0)*('ED Elec Income'!$E$2:$E$50000>=$D$2+0)*('ED Elec Income'!$E$2:$E$50000<=$D$4+0)
this will automatically generate a #NUM error.
Thats why the IFERROR is used to suppress the #NUM errors generated when there is no more data to list that match the conditions.
In a blank spreadsheet put a b c in A1 A3 A5 respectively.
in B1
=IFERROR(INDEX($A$1:$A$100,SMALL(IF(($A$1:$A$100<>""),ROW($A$1:$A$100)),ROW(A1)),1),"")
Array formula
This creates a list of abc in column B with no blanks.
Now remove the IFERROR and you'll find the #NUMs appear
Yes you're right. I didn't check it properly. Thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks