Adding up Column in a Table if the cell has a value

[Front]

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?

 

[jasonb75]

because it will pull in value from the Reserve Requirement cell when I don't want it to.

No, it will not. That row will have a zero value when the formula evaluates!

As I said earlier,

The only part that may not be 'skipped' correctly (depending on requirement) is the last part, SUM(Table13425[Reserve Requirement]) which will still include all rows.

I specifically included the part with the SUM() function as it is only that part which is affected, not the rest of the formula. If the same rows need to be omitted there as well then it can be done easily by using sumif instead of sum with a ">0" criteria on the close price column.

 

[Front]

Thanks again for your help, I added the SUMIF to the Reserve requirement row and it worked. At least I think, will do a bit more testing.

 

[jasonb75]

I added the SUMIF to the Reserve requirement row and it worked.

To the row or to the formula?

=SUMPRODUCT(IF(Table13425[Close Price Per Contract / Share]>0,(Table13425[ROC %]*(Table13425[Reserve Requirement])/SUMIF(Table13425[Close Price Per Contract / Share],">0",Table13425[Reserve Requirement]))))

This should also give the same result without array confirmation, note that I've used a different configuration and that there are no mathematical operations (+ - * / etc) in the sumproduct function other than the logical test coercion.

=SUMPRODUCT(--(Table13425[Close Price Per Contract / Share]>0),Table13425[ROC %],Table13425[Reserve Requirement])/SUMIF(Table13425[Close Price Per Contract / Share],">0",Table13425[Reserve Requirement])