Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))
Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
The two ranges are not the same size
E103:P114 is 12 by 12 while E98 by P98 is 12 by 1 Tell us more about the project's data layout best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
I can't reproduce the problem.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Any errors in any of those cells?
And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
I think I figured out the problem here. Some cells in the first column on
the range E103:P114 are blank. Entering 0 in those blank cells eliminated the error. Dave -- Brevity is the soul of wit. "Bernard Liengme" wrote: The two ranges are not the same size E103:P114 is 12 by 12 while E98 by P98 is 12 by 1 Tell us more about the project's data layout best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Turns out that the issue is some of the cells in the left-most column of the
range E103:P114 were blank; changing these blank cells to 0 eliminated the problem. Dave -- Brevity is the soul of wit. "Bob Phillips" wrote: I can't reproduce the problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Non-numeric, yes. Blanks, to be specific. See my responses to Bob and
Bernard. Thanks for the help. Dave -- Brevity is the soul of wit. "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Not a problem Bernard, but it is one of those cases where -- will not work,
but * does (don't mention this to RagDyer) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bernard Liengme" wrote in message ... The two ranges are not the same size E103:P114 is 12 by 12 while E98 by P98 is 12 by 1 Tell us more about the project's data layout best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Dave, assuming I have blank and text in the range the sumproduct formula is
pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
I agree with Bob, I couldn't duplicate your problem.
BUT ... now we can get into XL semantics ... where I have a disagreement with Harlan. What do *you* mean by "blank" That is, as opposed to "empty". Do you have formulas that equate to zero length strings ( "" or nulls)? That *to me* is "blank". OR Do the cells contain *nothing* ... truly virgin? That to me is *empty*. I couldn't duplicate your problem with *EMPTY* cells within E103:P114. Your original formula worked perfectly. However, when I entered zero length strings ( "" ), your formula returned the #VALUE! error. If you have formulas that equate to zero length strings ( "" or nulls) within the data range, and you might wish to keep those cells *blank* in appearance, as opposed to displaying 0's, you might try this formula: =SUMPRODUCT((E98:P98="Project")*(E103:P114<""),E1 03:P114) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Dave F" wrote in message ... Turns out that the issue is some of the cells in the left-most column of the range E103:P114 were blank; changing these blank cells to 0 eliminated the problem. Dave -- Brevity is the soul of wit. "Bob Phillips" wrote: I can't reproduce the problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
See RagDyer's response. Blank cells do not cause a problem, but spaces or
cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Rag--re semantics: as far as I can tell the cells were zero length strings,
which, to me, seems empty. The workbook was inherited from someone else and perhaps these cells originally contained something. I have no idea. But as I say, inserting 0 into these blank/empty/null/whatever cells eliminated the problem. -- Brevity is the soul of wit. "RagDyeR" wrote: I agree with Bob, I couldn't duplicate your problem. BUT ... now we can get into XL semantics ... where I have a disagreement with Harlan. What do *you* mean by "blank" That is, as opposed to "empty". Do you have formulas that equate to zero length strings ( "" or nulls)? That *to me* is "blank". OR Do the cells contain *nothing* ... truly virgin? That to me is *empty*. I couldn't duplicate your problem with *EMPTY* cells within E103:P114. Your original formula worked perfectly. However, when I entered zero length strings ( "" ), your formula returned the #VALUE! error. If you have formulas that equate to zero length strings ( "" or nulls) within the data range, and you might wish to keep those cells *blank* in appearance, as opposed to displaying 0's, you might try this formula: =SUMPRODUCT((E98:P98="Project")*(E103:P114<""),E1 03:P114) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Dave F" wrote in message ... Turns out that the issue is some of the cells in the left-most column of the range E103:P114 were blank; changing these blank cells to 0 eliminated the problem. Dave -- Brevity is the soul of wit. "Bob Phillips" wrote: I can't reproduce the problem. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
I can't seem to get that to work Bob!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... See RagDyer's response. Blank cells do not cause a problem, but spaces or cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Odd, worked for me. I will re-check in the morning, late here now.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... I can't seem to get that to work Bob! -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... See RagDyer's response. Blank cells do not cause a problem, but spaces or cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
BTW, I couldn't get the suggestion that you made to work, on the basis that
the numbers returned the #VALUE, so trying to outsort it in a condition made no difference. Will re-try that also. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... I can't seem to get that to work Bob! -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... See RagDyer's response. Blank cells do not cause a problem, but spaces or cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
The above post was 4:08 PM my time.
What was your time of the post? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... BTW, I couldn't get the suggestion that you made to work, on the basis that the numbers returned the #VALUE, so trying to outsort it in a condition made no difference. Will re-try that also. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... I can't seem to get that to work Bob! -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... See RagDyer's response. Blank cells do not cause a problem, but spaces or cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
00:08am. That means that you are on the West Coast?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... The above post was 4:08 PM my time. What was your time of the post? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... BTW, I couldn't get the suggestion that you made to work, on the basis that the numbers returned the #VALUE, so trying to outsort it in a condition made no difference. Will re-try that also. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... I can't seem to get that to work Bob! -- Regards, RD ------------------------------------------------------------------------- - - Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- - - "Bob Phillips" wrote in message ... See RagDyer's response. Blank cells do not cause a problem, but spaces or cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this formula return a #VALUE! error
Yes, the "Left" coast, among these "dang" Liberals in the L.A. area.
Heck ... now I've revealed my politics as well as my address!<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Bob Phillips" wrote in message ... 00:08am. That means that you are on the West Coast? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... The above post was 4:08 PM my time. What was your time of the post? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... BTW, I couldn't get the suggestion that you made to work, on the basis that the numbers returned the #VALUE, so trying to outsort it in a condition made no difference. Will re-try that also. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RagDyer" wrote in message ... I can't seem to get that to work Bob! -- Regards, RD ------------------------------------------------------------------------- - - Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- - - "Bob Phillips" wrote in message ... See RagDyer's response. Blank cells do not cause a problem, but spaces or cells with zero-length strings do. You can circumvent with =SUMPRODUCT((N(E103:P314))*(E98:P98="Project")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Dave, assuming I have blank and text in the range the sumproduct formula is pulling from, and that I can't change the ranges, is there anyway to avoid the associated error by modifying the sumproduct formula? Thanks. Steve C "Dave Peterson" wrote: Any errors in any of those cells? And non-numeric data in the first range? Dave F wrote: Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project")) Should return 0. But I also get the error if there are values greater than zero in the range which meet the criteria. Thanks, Dave -- Brevity is the soul of wit. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Why Does This Formula Return an Error?? | New Users to Excel | |||
Recurring Excel Formula error - multiple users affected! | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions |