0

I'm attempting to calculate the weighted average but getting an "#Error".

My current formula is outside the ground in the tablix is:

=SUM(Fields!EstimatedEPSGrowth.Value*(Fields!ProposedValue.Value/SUM(IIF(IsNothing(Fields!EstimatedEPSGrowth.Value),0, Fields!ProposedValue.Value))))

The If statement is there because not every line may have an EPS Growth, in case the EPS Growth will be set to 0 for that particular security

user172839
  • 1,035
  • 1
  • 10
  • 19
  • It seems that when EPS growth is not null it is actually dividing into proposed value; is that what you actually want to do? if so, are you sure proposed value is never null or 0? – Jayvee Oct 17 '17 at 08:50
  • It seems that if EstimatedEPSGrowth is `nothing` then you are trying to divide by zero. – Alan Schofield Oct 17 '17 at 11:28
  • This is the correct version as I pasted the wrong, but still getting the same #Error. =SUM(Fields!EstimatedEPSGrowth.Value*(IIF(IsNothing(Fields!EstimatedEPSGrowth.Value),0, Fields!ProposedValue.Value/SUM(IIF(IsNothing(Fields!ProposedValue.Value),0,Fields!ProposedValue.Value))))) – user172839 Oct 18 '17 at 02:14
  • If ProposedValue is nothing then there will be a division by zero, you may need to put IsNothing around the SUM. – Jayvee Oct 18 '17 at 08:48

1 Answers1

0

I think you should check whether Fields!Proposed‌​Value.Value=0 if yes then 0 else Fields!ProposedValue.Value/SUM(Field‌​s!ProposedValue.Valu‌​e)

I have written the code for you

SUM(
Fields!EstimatedEPSGrowth.Value*
(
IIF(IsNothing(Fields!E‌​stimatedEPSGrowth.Va‌​lue),0, IIF(IsNothing(Fields!Proposed‌​Value.Value),0,Fields!ProposedValue.Value/SUM(Field‌​s!ProposedValue.Valu‌​e)))
)
)
NiveaGM
  • 249
  • 2
  • 11