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]

Do any of the cells contain text? I can't see any other reason for the first one to return a #VALUE! error.

** Note if any of the cells contain formulas that return blanks then they are text **

 

[Front]

Both formulas that I thought should work only work as long as there are no empty rows. If there are any empty rows to skip it gives me #VALUE!. The cell that I need to check if empty does not have a formula in it. It is totally empty.

 

[Front]

I did more testing and I think I figured out the issue, although I dont know how to solve it. Both formulas that I think should work are still looking for a value in Table13425[ROC %] when that row is to be skipped. Basically the formula is not skipping that row when it should.

 

[jasonb75]

That shouldn't make a difference, it should still return a result even if it is not the correct one.

I have set up a test table and can not get the formula to return a #VALUE! error unless either the ROC % or Reserve Requirement columns contain text (or formulas that return text) but this would also cause the original formula that wasn't skipping blanks to produce the same error.

Basically the formula is not skipping that row when it should.

Formulas don't skip rows, they have a zero value when the criteria is not met.

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.

What happens if you use the last formula in post 1 and array confirm it with Ctrl Shift Enter?

 

[Front]

Thanks for your feedback. I definitely do not have text in the column that I want to considered when skipping that row. I do have formulas in the value rows, but that is why I am not trying to count that row if Table13425[Close Price Per Contract / Share] is empty. Is there no way to do that? I made a short video to explain.

 

[jasonb75]

I do have formulas in the value rows,

This is why i asked

Do any of the cells contain text? I can't see any other reason for the first one to return a #VALUE! error.

** Note if any of the cells contain formulas that return blanks then they are text **

It doesn't need a video to explain, just you to read the questions asked properly and answer them accurately.

Which would include

What happens if you use the last formula in post 1 and array confirm it with Ctrl Shift Enter?

As that should work correctly unless you have a formula that is returning text in a row where the first column is not empty.

 

[Front]

Okay, so I need those formulas in there. I think now that I understand the problem I can probably write an if statement to fix this. Thank you for the help!

 

[jasonb75]

so I need those formulas in there.

I didn't, at any time say that you would need to remove them, which is what that response implies.

I don't know how much simpler I can make it. This formula from your first post will (as far as I can see from the information provided) work if you array confirm it with Ctrl Shift Enter

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

 

[Front]

Thank you, but that formula does not work because it will pull in value from the Reserve Requirement cell when I don't want it to. That cell will get a value as soon as I open a trade, but it don't want it to get added into the calculation until there is a closing trade. That is why I must skip that cell if there is no closing price filled out on that row.