Help with Embedded If statement

[cdnqte]

I am hoping someone can help!

I have a formula that in some cases is returning a #div/0! error.

in it's simplest form in know how to enter the formula to make that a 0. So if my formula were nextbillamount/nextbilldays I would enter if(n(nextbilldays),nextbillamount/nextbildays,0) and I get 0 instead of the error on all rows where nextbildays = 0.

However, my cell is a little more complicated than that and I am struggling with the embedded ifs.... here is how it looks right now...


=IF([@[Billing Adjustment Needed?]]="Adv",[@[Next Bill Amount]]/[@[Next Bill Days Adv]],[@[Next Bill Amount]]/[@[Next Bill Days Arr]])

I need to get the additional if statement in there to eliminate the error when the next bill days adv field is 0.

Can someone assist? Embedded ifs mess me up all the time!

Thanks in advance!

 

[teksp0rt]

you could use the =iferror function... =iferror(formula, value you want if you receive and error).

=iferror(IF([@[Billing Adjustment Needed?]]="Adv",[@[Next Bill Amount]]/[@[Next Bill Days Adv]],[@[Next Bill Amount]]/[@[Next Bill Days Arr]]),0)

 

[teksp0rt]

you could use the =iferror function... =iferror(formula, value you want if you receive and error).

=iferror(IF([@[Billing Adjustment Needed?]]="Adv",[@[Next Bill Amount]]/[@[Next Bill Days Adv]],[@[Next Bill Amount]]/[@[Next Bill Days Arr]]),0)

.... this would give you a 0 value.

 

[teksp0rt]

or... here is the nested IF...

=(IF([@[Next Bill Days Adv]]=0,0,IF([@[Billing Adjustment Needed?]]="Adv" [@[ (IF([@[Billing Adjustment Needed?]]="Adv",[@[Next Bill Amount]]/[@[Next Bill Days Adv]],[@[Next Bill Amount]]/[@[Next Bill Days Arr]]))

 

[cdnqte]

Thank you! That did the trick!