dividing calculation

[bartoni]

HI, is there anyway anyone can help me?

Ive currently calculating 10 quarters of return on investment using quarterly sales divided by quartely promotion.

If a product has 0 sales and 0 promo, the value returned is DIV!0 not 0.

If a product does not have 10 quarters of data, both sales and promo will be blank and consequently the calculation returns a DIV!0 error.

How do i make it so that zero divided by zero will return a 0
and blank divided by blank returns a DIV!0 error.

Is the return error custonisable?

Thanks

 

[mianmazher]

Place your formula in =ISNA()

I think it will solve ur problem


Regards


Mian Mazher

 

[Aladin Akyurek]

Care to post one of the formulas that gives you #DIV/0! ?

 

[bartoni]

Here it is a simple division formula

=E2/N2

Ive tried the =ISNA(E2/N2) but it just says false.

Thanks

 

[onlyadrafter]

Hello,

I don't think ISNA will work for no values, I believe you need iserror

e.g.

=IF(ISERROR(E9/F9),0,E9/F9)

Change cell refs and formula to suit.

 

[mianmazher]

=if(isna(yourformula),yourformula,0)

hope u will understand


Regards


Mian Mazher

 

[Aladin Akyurek]

Here it is a simple division formula

=E2/N2

...

=IF(N2,E2/N2,0)

If N2 can house a rext value like "", then use:

=IF(N(N2),E2/N2,0)