Help on formula

[kelljeff]

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*AP3,AR3*AV3,AX3*BB3,BD3*BH3,BJ3*BN3,BP3*BT3,BV3*BZ3,CB3*CF3,CH3*CL3,CN3*CR3,CT3*CX3,CZ3*DD3,DF3*DJ3,DL3*DP3,DR3*DV3,DX3*EB3,ED3*EH3)/EN3

Any help would be great.

 

[EdMac]

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

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

E

 

[Bondi]

Hi

You van use

IF(ISERROR(YourFormula),0,YourFormula)

Regards,
Bondi

 

[kelljeff]

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,AR3*AV3,AX3*BB3,BD3*BH3,BJ3*BN3,BP3*BT3,BV3*BZ3,CB3*CF3,CH3*CL3,CN3*CR3,CT3*CX3,CZ3*DD3,DF3*DJ3,DL3*DP3,DR3*DV3,DX3*EB3,ED3*EH3)/EN3)

 

[kelljeff]

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*AJ4,AL4*AP4,AR4*AV4,AX4*BB4,BD4*BH4,BJ4*BN4,BP4*BT4,BV4*BZ4,CB4*CF4,CH4*CL4,CN4*CR4,CT4*CX4,CZ4*DD4,DF4*DJ4,DL4*DP4,DR4*DV4,DX4*EB4,ED4*EH4)/EN4),0,((B4*F4,H4*L4,N4*R4,T4*X4,Z4*AD4,AF4*AJ4,AL4*AP4,AR4*AV4,AX4*BB4,BD4*BH4,BJ4*BN4,BP4*BT4,BV4*BZ4,CB4*CF4,CH4*CL4,CN4*CR4,CT4*CX4,CZ4*DD4,DF4*DJ4,DL4*DP4,DR4*DV4,DX4*EB4,ED4*EH4)/EN4))

 

[EdMac]

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

 

[Pete_UK]

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*DD3,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