PDA

View Full Version : Sleeper: Formula calculate value



sjvenz
09-09-2005, 08:13 AM
What I need to be able to do is calculate the correct value

The formula I have to us is Gross Weight - Net Weight / Wagon Weight


What I have sofar is this


=IF(SUM(O13,R13)=AB13,IF(K13=5,HLOOKUP(AC13,SA,5,FALSE),IF(K13=4,HLOOKUP(AC 13,SA,12,FALSE),"False")))

This formula give me the correct value when the weights are correct but if their is a mixture of wagon configuration then the Gross-Net/Wagon Weight comes into effect


=SUM((12005-((O13+Q13)*106)+((P13+R13)*90)+33/IF(K13=5,HLOOKUP(AC13,SA,5,FALSE),HLOOKUP(AC13,SA,12,FALSE))))

12005 is the Gross

((O13+Q13)*106)+((P13+R13)*90)+33 - gives me the NET

IF(K13=5,HLOOKUP(AC13,SA,5,FALSE),HLOOKUP(AC13,SA,12,FALSE) - gives me the Wagon Weight.

I get a "div/0 error when trying to work out the mixed formula, have tried a few thing but have had no luck does anyone have any ideas where I went wrong or even if it can be shrtened:bug:

Zack Barresse
09-09-2005, 08:24 AM
DIV/0 error means you have a zero as either the denominator. Having a zero as the numerator will only return a zero. So your denominator must be 0. To fix this, you can use the MAX formula, something like this ...



=Numerator/MAX(1,Denomenator)

Note this will always return the Numerator so long as the Denomenator is zero. It is your discretion as to how you want to handle it from there.