=IF(H7="Long",-IF($D$1="Flat",AVERAGEIFS('3'!$Q$3:$Q$300,'3'!$C$3:$C$300,">="&$D7,'3'!$C$3:$C$300,"<="&$E7),SUMPRODUCT('3'!$Q$3:$Q$300,'3'!$V$3:$V$300)/SUM('3'!$V$3:$V$300)),IF($D$1="Flat",AVERAGEIFS('3'!$Q$3:$Q$300,'3'!$C$3:$C$300,">="&$D7,'3'!$C$3:$C$300,"<="&$E7),SUMPRODUCT('3'!$Q$3:$Q$300,'3'!$V$3:$V$300)/SUM('3'!$V$3:$V$300)))
The above is my formula in excel. It basically calculates the weighted average value of something. I am trying to add a criteria where it will ignore blank cells and 0's. I have tried a few things but nothing is seeming to work for me. Does anyone have any suggestions?
Thank you,
The above is my formula in excel. It basically calculates the weighted average value of something. I am trying to add a criteria where it will ignore blank cells and 0's. I have tried a few things but nothing is seeming to work for me. Does anyone have any suggestions?
Thank you,