Combine Formulas

Billa7
Billa7 used Ask the Experts™
on
Is that a way to combine all formulas from Column F:H (Detail sheet) in Column E and produced the same result as in Column H.  Hope Experts could help.

Combine Formulas
Formula---Copy.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

try

=(D3-(D3-IF(TODAY()<$B3,"",D3-(SUMPRODUCT((Data!$B$2:$B$331<=$C3)*(Data!$D$2:$D$331=F$2)*(Data!$I$2:$I$331="Yes")*Data!$H$2:$H$331)))))/D3*100

cheers, teylyn
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you use a comma before the last term in the SUMPRODUCT, it will become tolerant of text in Data column H. And if you eliminate the extra D3 from the formula you can shorten it to:
=IF(TODAY()<$B3,"",100*(1-SUMPRODUCT((Data!$B$2:$B$331<=$C3)*(Data!$D$2:$D$331=F$2)*(Data!$I$2:$I$331="Yes"),Data!$H$2:$H$331)/D3))

Author

Commented:
Hi byundt,

How to prevent error message “#DIV/0!” if the cell at Column D is empty?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You might consider:
=IF(OR(TODAY()<$B3,D3=""),"",100*(1-SUMPRODUCT((Data!$B$2:$B$331<=$C3)*(Data!$D$2:$D$331=F$2)*(Data!$I$2:$I$331="Yes"),Data!$H$2:$H$331)/D3))

Author

Commented:
Hi byundt,

Thanks for the revised formula. The data only appeared at Column E if the preceding cell at Column D with data. E.g. Cell E3 will be empty if no data at Cell D4 even though cell D3 with data. How to fix this?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I think you are asking how to fix the formula in cell E3 so it doesn't populate unless data entered in D3:
=IF(OR(TODAY()<$B3,D3=""),"",SUM($D$3:$D3)-(SUMPRODUCT((Data!$B$2:$B$331<=$C3)*(Data!$D$2:$D$331=E$2)*(Data!$I$2:$I$331="Yes"),Data!$H$2:$H$331)))

Author

Commented:
Thanks a lot for the help. Sorry for the delay

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial