Will someone look at this and tell me why I have a #VALUE! error

  • Thread starter Thread starter hybridthinker
  • Start date Start date
I looked back through it again and can't seem to figure out what the
problem is....so it will probably end up being an extra comma or
something...
 
It's better to use the comma syntax for doing a multiconditional summing
(when the shape of the ranges involved is identical)...

So...

=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A3),(WrkRecGP
Hr)))

instead of

=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A3)*(WrkRecGP
Hr)))

The comma syntax allows for text values like formula blanks ("") in the
range to sum, the star syntax obviously not.
 
I'm confused...I've either been looking at code to long, or I don't know
how to fix it....the error is on the monthly and yearly report tabs.
Thanks in advance.

File Attached: http://www.excelforum.com/attachment.php?postid=318999 (payroll.zip)
Click to expand...


You are using the wrong syntax.

Syntax

SUMPRODUCT(array1,array2,array3, ...)

You are using:


=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A3)*(WrkRecSalary)))

Try:


=IF(OR(A3="",D3=""),"",SUMPRODUCT((WrkRecMonth=$A$1),(WrkRecID=A3),(WrkRecGPHr)))




--ron
 
When the formulas in the blank rows on the workrecord ws were deleted, it
works.
So, goto the row number indicator and clearcontents. However, this caused
ref on e2:d6 on yearly report so fix that first.
 
There are many things that needs to be changed:

1) Dynamic named ranges are not defined properly.
2) You carry out expensive checks by computing the same thing twice.
3) You're using a SumProduct formula while a SumIf formula would suffice and
be more efficient.
4) In your updated version you didn't change all SumProduct formulas for
conditional summing to one that uses the comma syntax:

SUMPRODUCT((CondtionalExpression1)*(ConditionalExpression2),RangeToSum)
 
thanks for looking...if you made the changes on a file could you please
repost....
 
So...has anyone came up with some ideas? I see that 4 people have
downloaded the latest update...just curious...
 
I suspect most responders are reading this in the
microsoft.public.excel.worksheet.functions news group and can't post an
attachment back to the Excelforum.

Perhaps if you posted your email address, someone could email you the
corrected workbook.
 
If you have done anywork to this file please forward it to
(e-mail address removed), if you fixed it fully or know how please just put
the fix in one cell and I will do the rest. I really don't know what
else to do...thats why I'm asking for help.
 
I recieved two suggestions for solving my problems....I'm just wondering
if any of the 30 people who have downloaded it have come up with any of
their own conclusions?
 
The problem with June not working in the blank lines in WorkRecord
worksheet. The range for you defined names is not enough rows. If the
cells in the enpty rows were not blank your code would work.

I think I got the formulas working by adding a N() around the
WrkRecMonth and WrkRecID arguments to SUMPRODUCT

From Monthly
=IF(OR(A4="",D4=""),"",SUMPRODUCT((WrkRecMonth=$A$1)*(WrkRecID=A4),(WrkRecSalary)))


From Yearly
=IF(OR(A2="",D2=""),"",SUMPRODUCT(N((WrkRecID=A2)),(WrkRecGPHr)))

I did not test the values to check if formulas were working correctly.
 
I found that the N() with the conversion of the * to a , worked for me.
I never used the N() that way. Great idea!
 
Back
Top