Hi Everyone, I am having a hard time getting this weighted average formula to work with my table. Been working on this for about 5 hours and cannot believe I cannot get this to work...
I am trying to say if the cell has no value then skip that row, but if it does have a value then get the value from another cell in that row and divide it by the total of all those cells.
So this formula works perfectly if there are no rows to skip.
=SUMPRODUCT(Table13425[ROC %]*(Table13425[Reserve Requirement]/SUM(Table13425[Reserve Requirement])))
But I need it to skip rows that have empty cells in column Table1342[Close Price Per Contract / Share]
So I tried this, seems like it should have worked based on what I read. But did not.
=SUMPRODUCT(--(Table13425[Close Price Per Contract / Share]>0),Table13425[ROC %]*(Table13425[Reserve Requirement]/SUM(Table13425[Reserve Requirement])))
I also tried this and it did not.
=SUMPRODUCT(IF(Table13425[Close Price Per Contract / Share]>0,(Table13425[ROC %]*(Table13425[Reserve Requirement])/SUM(Table13425[Reserve Requirement]))))
The both just return #VALUE!.
Can anyone shed some light on this?
I am trying to say if the cell has no value then skip that row, but if it does have a value then get the value from another cell in that row and divide it by the total of all those cells.
So this formula works perfectly if there are no rows to skip.
=SUMPRODUCT(Table13425[ROC %]*(Table13425[Reserve Requirement]/SUM(Table13425[Reserve Requirement])))
But I need it to skip rows that have empty cells in column Table1342[Close Price Per Contract / Share]
So I tried this, seems like it should have worked based on what I read. But did not.
=SUMPRODUCT(--(Table13425[Close Price Per Contract / Share]>0),Table13425[ROC %]*(Table13425[Reserve Requirement]/SUM(Table13425[Reserve Requirement])))
I also tried this and it did not.
=SUMPRODUCT(IF(Table13425[Close Price Per Contract / Share]>0,(Table13425[ROC %]*(Table13425[Reserve Requirement])/SUM(Table13425[Reserve Requirement]))))
The both just return #VALUE!.
Can anyone shed some light on this?
Last edited: