| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help on Formula
I've data copied from a .CSV file and pasted it to an excel sheet.
Column A-G is copied from .CSV File and Column H is calculated using formula (=D1/F1/24) to find the items per hour. Column "D" is number of Items and Column "F" is Time in HH:MM:SS format. But when I use the formula "=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)" in Cell "L5" i get #DIV/0! and Same formula works well if I manually enter data in Column "H" instead of using (=D1/F1/24).. Could someone please help me with this. Thanks.. -- Karthi |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on Formula
You've got #DIV/0! errors in col H. Clean it up, or include an error trap for
the formula in col H to return errors as zeros, eg: =IF(ISERROR(D1/F1/24),0,D1/F1/24) Alternatively, as-is, you could try this array-entered* equivalent: =SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*( B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999))) *press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "Karthik" wrote: I've data copied from a .CSV file and pasted it to an excel sheet. Column A-G is copied from .CSV File and Column H is calculated using formula (=D1/F1/24) to find the items per hour. Column "D" is number of Items and Column "F" is Time in HH:MM:SS format. But when I use the formula "=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)" in Cell "L5" i get #DIV/0! and Same formula works well if I manually enter data in Column "H" instead of using (=D1/F1/24).. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on Formula
Col "H" does'nt have Zeros or #DIV/0!,
Sumproduct formula gives #DIV/0! error if i calculate Col "H" with formula (=D1/F1/24). Example: D F H 10 0:10:00 60.0000 If col "D1" is 10 and Col "F1" is 10 Minutes and col "H" is (D1/F1/24)=60.0000 per hour Sumproduct formula works well if I manually enter 60.0000 in Column H and it gives #DIV/0! if its calculated by the formula (=d1/f1/24) -- Karthi "Max" wrote: You've got #DIV/0! errors in col H. Clean it up, or include an error trap for the formula in col H to return errors as zeros, eg: =IF(ISERROR(D1/F1/24),0,D1/F1/24) Alternatively, as-is, you could try this array-entered* equivalent: =SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*( B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999))) *press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "Karthik" wrote: I've data copied from a .CSV file and pasted it to an excel sheet. Column A-G is copied from .CSV File and Column H is calculated using formula (=D1/F1/24) to find the items per hour. Column "D" is number of Items and Column "F" is Time in HH:MM:SS format. But when I use the formula "=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)" in Cell "L5" i get #DIV/0! and Same formula works well if I manually enter data in Column "H" instead of using (=D1/F1/24).. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on Formula
You can't get #DIV/0! from
=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999) unless column H contains #DIV/0! errors. You'd better look again. -- David Biddulph Karthik wrote: Col "H" does'nt have Zeros or #DIV/0!, Sumproduct formula gives #DIV/0! error if i calculate Col "H" with formula (=D1/F1/24). Example: D F H 10 0:10:00 60.0000 If col "D1" is 10 and Col "F1" is 10 Minutes and col "H" is (D1/F1/24)=60.0000 per hour Sumproduct formula works well if I manually enter 60.0000 in Column H and it gives #DIV/0! if its calculated by the formula (=d1/f1/24) You've got #DIV/0! errors in col H. Clean it up, or include an error trap for the formula in col H to return errors as zeros, eg: =IF(ISERROR(D1/F1/24),0,D1/F1/24) Alternatively, as-is, you could try this array-entered* equivalent: =SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*( B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999))) *press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "Karthik" wrote: I've data copied from a .CSV file and pasted it to an excel sheet. Column A-G is copied from .CSV File and Column H is calculated using formula (=D1/F1/24) to find the items per hour. Column "D" is number of Items and Column "F" is Time in HH:MM:SS format. But when I use the formula "=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)" in Cell "L5" i get #DIV/0! and Same formula works well if I manually enter data in Column "H" instead of using (=D1/F1/24).. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on Formula
Thanks David, I failed to notice that..
-- Karthi "David Biddulph" wrote: You can't get #DIV/0! from =SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999) unless column H contains #DIV/0! errors. You'd better look again. -- David Biddulph Karthik wrote: Col "H" does'nt have Zeros or #DIV/0!, Sumproduct formula gives #DIV/0! error if i calculate Col "H" with formula (=D1/F1/24). Example: D F H 10 0:10:00 60.0000 If col "D1" is 10 and Col "F1" is 10 Minutes and col "H" is (D1/F1/24)=60.0000 per hour Sumproduct formula works well if I manually enter 60.0000 in Column H and it gives #DIV/0! if its calculated by the formula (=d1/f1/24) You've got #DIV/0! errors in col H. Clean it up, or include an error trap for the formula in col H to return errors as zeros, eg: =IF(ISERROR(D1/F1/24),0,D1/F1/24) Alternatively, as-is, you could try this array-entered* equivalent: =SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*( B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999))) *press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "Karthik" wrote: I've data copied from a .CSV file and pasted it to an excel sheet. Column A-G is copied from .CSV File and Column H is calculated using formula (=D1/F1/24) to find the items per hour. Column "D" is number of Items and Column "F" is Time in HH:MM:SS format. But when I use the formula "=SUMPRODUCT(--(A$2:A$9999=L$5),--(B$2:B$9999=L$6),--(C$2:C$9999=K8),H$2:H$9999)" in Cell "L5" i get #DIV/0! and Same formula works well if I manually enter data in Column "H" instead of using (=D1/F1/24).. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help on Formula
Karthik,
Doesn't the alternative array-entered expression given in my earlier response: =SUM(IF(ISNUMBER(H$2:H$9999),IF((A$2:A$9999=L$5)*( B$2:B$9999=L$6)*(C$2:C$9999=K8),H$2:H$9999))) do the job for you, as-is ? I tested it fine here, it evaluates correctly in the face of possible #DIV/0! errors in col H. And it should work for you just as well. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
