same formula different result

M

Micayla Bergen

i have the same formula in two cells w one small difference, and yet one
returns a DIV/0 error and the other doesnt.
D3 is this =IF(F3=0,H3/$H$39,"")
E3 is this =IF(F3=1,I3/$H$39, "")
where F3 is this =IF(ISERROR((VLOOKUP(A3,'Model Portfolio'!$A$3:$A$51,1,
FALSE))),0,1)
H3 is this =IF(AND(B3=1,F3=0),G3*C3,0)
I3 is this =IF(AND(B3=1,F3=1),G3*C3,0)
and H39 is this =SUM(H3:I38)

i cannot figure out why D3 gives the Div/0 error and E3 does not
thank you
 
D

Dave Peterson

Since you're dividing by $h$39, I would have guessed you would have used:

=if($h$39=0,"",h3/$h$39)

I don't see why F3 is part of your formula.
 
M

Micayla Bergen

Hi Dave
F3 identifies whether A is in the model - 1, or not - 0
H39 is the total value of the portfolio
D3 is the % of the portfolio value that is not in the model
E3 is the % of the portfolio value that is in the model
so the value of A will go in either D or E depending on if F is 1 or 0
 
D

Dave Peterson

You'll still want to examine the denominator to make sure it's not 0.

=IF(F3=0,IF($H$39=0,"0 in h39",H3/$H$39),"")

(I wasn't sure what should happen if H39 = 0, though.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top