Help on formula
 

Hello Everyone,

I need help... Below is my obnoxious formula.

What can I add to it so when I get #DIV/0 It returns a 0 instead.
=SUM(B3*F3,H3*L3,N3*R3,T3*X3,Z3*AD3,AF3*AJ3,AL3*AP 3,AR3*AV3,AX3*BB3,BD3*BH3,BJ3*BN3,BP3*BT3,BV3*BZ3, CB3*CF3,CH3*CL3,CN3*CR3,CT3*CX3,CZ3*DD3,DF3*DJ3,DL 3*DP3,DR3*DV3,DX3*EB3,ED3*EH3)/EN3

Any help would be great.


--
kelljeff
------------------------------------------------------------------------
kelljeff's Profile: http://www.excelforum.com/member.php...o&userid=32007
View this thread: http://www.excelforum.com/showthread...hreadid=529992

Help on formula
 

Presume the problem is EN3 being zero so....

Try wrapping it in an IF statement so that if(EN3=0,0,Your formula)

Ed


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=529992

Help on formula
 

Hi

You van use

IF(ISERROR(YourFormula),0,YourFormula)

Regards,
Bondi

Help on formula
 

It's partially working.

If the EN5 has a number the formula is coming back with a #VALUE error.


Am I doing it wrong?

=IF(EN3=0,0
(B3*F3,H3*L3,N3*R3,T3*X3,Z3*AD3,AF3*AJ3,AL3*AP3,AR 3*AV3,AX3*BB3,BD3*BH3,BJ3*BN3,BP3*BT3,BV3*BZ3,CB3* CF3,CH3*CL3,CN3*CR3,CT3*CX3,CZ3*DD3,DF3*DJ3,DL3*DP 3,DR3*DV3,DX3*EB3,ED3*EH3)/EN3)


--
kelljeff
------------------------------------------------------------------------
kelljeff's Profile: http://www.excelforum.com/member.php...o&userid=32007
View this thread: http://www.excelforum.com/showthread...hreadid=529992

Help on formula
 

I tryed the ISERROR function also... However I'm haveing the same
problem.

=IF(ISERROR((B4*F4,H4*L4,N4*R4,T4*X4,Z4*AD4,AF4*AJ 4,AL4*AP4,AR4*AV4,AX4*BB4,BD4*BH4,BJ4*BN4,BP4*BT4, BV4*BZ4,CB4*CF4,CH4*CL4,CN4*CR4,CT4*CX4,CZ4*DD4,DF 4*DJ4,DL4*DP4,DR4*DV4,DX4*EB4,ED4*EH4)/EN4),0,((B4*F4,H4*L4,N4*R4,T4*X4,Z4*AD4,AF4*AJ4,AL 4*AP4,AR4*AV4,AX4*BB4,BD4*BH4,BJ4*BN4,BP4*BT4,BV4* BZ4,CB4*CF4,CH4*CL4,CN4*CR4,CT4*CX4,CZ4*DD4,DF4*DJ 4,DL4*DP4,DR4*DV4,DX4*EB4,ED4*EH4)/EN4))


--
kelljeff
------------------------------------------------------------------------
kelljeff's Profile: http://www.excelforum.com/member.php...o&userid=32007
View this thread: http://www.excelforum.com/showthread...hreadid=529992

Help on formula
 

Hi Kelljeff,

Looking at your posting where you say that if EN5 has a number it does
not work, I can't see a reference to EN5 so I guess this has to be a
input for a later calculation. Check that this cell is not formatted
as text as that could give the problem.

Ed


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=529992

Help on formula
 

You missed a comma and the SUM from this formula:

=IF(EN3=0,0,sum(B3*F3,H3*L3,N3*R3,T3*X3,Z3*AD3,AF3 *AJ3,AL3*AP3,AR3*AV3,AX3*BB3,BD3*BH3,BJ3**BN3,BP3* BT3,BV3*BZ3,CB3*CF3,CH3*CL3,CN3*CR3,CT3*CX3,CZ3*DD 3,DF3*DJ3,DL3*DP*3,DR3*DV3,DX3*EB3,ED3*EH3)/EN3)

You were getting #DIV/0 errors because you were trying to divide by 0 !
As you are only dividing by EN3, then the formula should correct this,
unless one of the other values you are trying to sum also have errors
in them.

Hope this helps.

Pete