Using Sumproduct when some of the values are null
in [Functions]
|
Prev: Justify across selection
Next: Data Validation List from a Dynamic Named Range on Another Workshe
From: SanCarlosCyclist on 23 Mar 2010 15:52 I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% $215,237 11 Emergency 22.73% $105,059 12 Emergency 8.46% $53,760 20 Emergency $563,258 My goal is to calculate an aggregate trend for the Emergency Category excluding those rows that are null trend in cell b20. I tried the following formula: SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20 The result s "#Value" Do you know how to get this to work?
From: Joe User on 23 Mar 2010 16:06 See your thread in m.p.excel. Do not multipost or split threads, especially when there is already people trying help you. It serves no useful purpose, and it can waste time since responders do not benefit from the other context. ----- original message ----- "SanCarlosCyclist" <sancarloscyclist(a)gmail.com> wrote in message news:353c670d-a55f-4e5a-8c82-a824f2d73179(a)z3g2000yqz.googlegroups.com... >I am having a problem using Sumproduct when some of the cells have > null values. I will try to describe the problem. Please let me know > if > you need additional information. Do any of you have any ideas of how > I > can fix that sumproduct to get it to work? > > A B C > Row Trend Claims > 5 Emergency $0 > 6 Emergency $0 > 7 Emergency $0 > 8 Emergency 81.68% $24,444 > 9 Emergency 35.00% $164,758 > 10 Emergency 35.00% $215,237 > 11 Emergency 22.73% $105,059 > 12 Emergency 8.46% $53,760 > > 20 Emergency $563,258 > > My goal is to calculate an aggregate trend for the Emergency Category > excluding those rows that are null trend in cell b20. I tried the > following formula: > SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20 > The result s "#Value" > Do you know how to get this to work?
From: JBoulton on 23 Mar 2010 16:45 This will work if you expect a result of 42.32%: =SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20 "SanCarlosCyclist" wrote: > I am having a problem using Sumproduct when some of the cells have > null values. I will try to describe the problem. Please let me know > if > you need additional information. Do any of you have any ideas of how > I > can fix that sumproduct to get it to work? > > A B C > Row Trend Claims > 5 Emergency $0 > 6 Emergency $0 > 7 Emergency $0 > 8 Emergency 81.68% $24,444 > 9 Emergency 35.00% $164,758 > 10 Emergency 35.00% $215,237 > 11 Emergency 22.73% $105,059 > 12 Emergency 8.46% $53,760 > > 20 Emergency $563,258 > > My goal is to calculate an aggregate trend for the Emergency Category > excluding those rows that are null trend in cell b20. I tried the > following formula: > SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20 > The result s "#Value" > Do you know how to get this to work? > . >
From: SanCarlosCyclist on 23 Mar 2010 17:22 On Mar 23, 1:45 pm, JBoulton <JBoul...(a)discussions.microsoft.com> wrote: > This will work if you expect a result of 42.32%: > =SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20 > > > > "SanCarlosCyclist" wrote: > > I am having a problem using Sumproduct when some of the cells have > > null values. I will try to describe the problem. Please let me know > > if > > you need additional information. Do any of you have any ideas of how > > I > > can fix that sumproduct to get it to work? > > > A B C > > Row Trend Claims > > 5 Emergency $0 > > 6 Emergency $0 > > 7 Emergency $0 > > 8 Emergency 81.68% $24,444 > > 9 Emergency 35.00% $164,758 > > 10 Emergency 35.00% $215,237 > > 11 Emergency 22.73% $105,059 > > 12 Emergency 8.46% $53,760 > > > 20 Emergency $563,258 > > > My goal is to calculate an aggregate trend for the Emergency Category > > excluding those rows that are null trend in cell b20. I tried the > > following formula: > > SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20 > > The result s "#Value" > > Do you know how to get this to work? > > .- Hide quoted text - > > - Show quoted text - Yesssssssssssssssss, it worked!! Woohoooooo!!!! Thanks so much for your help. This formula was driving me crazy.
From: Joe User on 23 Mar 2010 19:17 "SanCarlosCyclist" <sancarloscyclist(a)gmail.com> wrote: > On Mar 23, 1:45 pm, > JBoulton <JBoul...(a)discussions.microsoft.com> wrote: > > This will work if you expect a result of 42.32%: > > =SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20 [....] > Yesssssssssssssssss, it worked!! Woohoooooo!!!! Exactly the solution I provided and you read an hour earlier in the other thread. I'm glad you finally tried it. FYI, there is no need for the parentheses around the ranges. The best way to write that is: =SUMPRODUCT(--(A20=A5:A12),B5:B12,C5:C12)/C20 ----- original message ----- "SanCarlosCyclist" <sancarloscyclist(a)gmail.com> wrote in message news:b61c5f45-d59a-442e-914a-ac0869df6cbd(a)i25g2000yqm.googlegroups.com... On Mar 23, 1:45 pm, JBoulton <JBoul...(a)discussions.microsoft.com> wrote: > This will work if you expect a result of 42.32%: > =SUMPRODUCT(--(A20=A5:A12),(B5:B12),(C5:C12))/C20 > > > > "SanCarlosCyclist" wrote: > > I am having a problem using Sumproduct when some of the cells have > > null values. I will try to describe the problem. Please let me know > > if > > you need additional information. Do any of you have any ideas of how > > I > > can fix that sumproduct to get it to work? > > > A B C > > Row Trend Claims > > 5 Emergency $0 > > 6 Emergency $0 > > 7 Emergency $0 > > 8 Emergency 81.68% $24,444 > > 9 Emergency 35.00% $164,758 > > 10 Emergency 35.00% $215,237 > > 11 Emergency 22.73% $105,059 > > 12 Emergency 8.46% $53,760 > > > 20 Emergency $563,258 > > > My goal is to calculate an aggregate trend for the Emergency Category > > excluding those rows that are null trend in cell b20. I tried the > > following formula: > > SUMPRODUCT((A20=A5:A12)*(A5:A12)*b5:b12)/c20 > > The result s "#Value" > > Do you know how to get this to work? > > .- Hide quoted text - > > - Show quoted text - Yesssssssssssssssss, it worked!! Woohoooooo!!!! Thanks so much for your help. This formula was driving me crazy.
|
Next
|
Last
Pages: 1 2 Prev: Justify across selection Next: Data Validation List from a Dynamic Named Range on Another Workshe |