Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
same formula different result
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 |
#2
|
|||
|
|||
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. Micayla Bergen wrote: 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 -- Dave Peterson |
#3
|
|||
|
|||
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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: 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 -- Dave Peterson |
#4
|
|||
|
|||
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.) Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |