#DIV/0! error - trying to make formula conditional

Hi,

I am trying to work out the average loan size over a given number of
months from various referral sources

I am running the formula below

=(J3+N3+R3+V3+Z3+ad3+ah3+al3+ap3+at3+ax3+bb3)/COUNTA(J3,N3,R3,V3,Z3ad3+ah3+al3+ap3+at3+ax3+bb3)

However if all the cells are blank it returms the #DIV/0! error.

I understand why it does this so I am trying to make the formula
conditional on at least one of the cells having a value in it.

I have tried this formula but i suspect I am barking up the wrong
tree.

=if(or(J3="",N3="",R3="",V3="",Z3="",AD3="",AH3="" ,AL3="",AP3="",AT3="",AX3="",BB3=""),"",if((J3+N3+ R3+V3+Z3+ad3+ah3+al3+ap3+at3+ax3+bb3)/COUNTA(J3,N3,R3,V3,Z3ad3+ah3+al3+ap3+at3+ax3+bb3), "no
settlements"))

Any help would be greatly appreciated.

Chris


---
Message posted from http://www.ExcelForum.com/

christopherp wrote in
:

Hi,

I am trying to work out the average loan size over a given number
of months from various referral sources

I am running the formula below

=(J3+N3+R3+V3+Z3+ad3+ah3+al3+ap3+at3+ax3+bb3)/COUNTA(J3,N3,R3,V3,Z3
ad3+ah3+al3+ap3+at3+ax3+bb3)

However if all the cells are blank it returms the #DIV/0! error.

I understand why it does this so I am trying to make the formula
conditional on at least one of the cells having a value in it.

I have tried this formula but i suspect I am barking up the wrong
tree.

=if(or(J3="",N3="",R3="",V3="",Z3="",AD3="",AH3="" ,AL3="",AP3="",AT
3="",AX3="",BB3=""),"",if((J3+N3+R3+V3+Z3+ad3+ah3+ al3+ap3+at3+ax3+b
b3)/COUNTA(J3,N3,R3,V3,Z3ad3+ah3+al3+ap3+at3+ax3+bb3), "no
settlements"))

Any help would be greatly appreciated.

Chris


---
Message posted from http://www.ExcelForum.com/



Would this do the trick:

=IF(ISERROR((J3+N3+R3+V3+Z3+AD3+AH3+AL3+AP3+AT3+AX 3+BB3)/COUNTA
(J3;N3;R3;V3;Z3;AD3+AH3+AL3+AP3+AT3+AX3+BB3));"";( J3+N3+R3+V3+Z3+AD3
+AH3+AL3+AP3+AT3+AX3+BB3)/COUNTA(J3;N3;R3;V3;Z3;AD3+AH3+AL3+AP3+AT3
+AX3+BB3))

(And replace the ';' with ',')

I am not really sure what to make of your second formula, since the
second if-function (if the first if-function returns a "FALSE")
doesn't have a logical test...

HTH,

CoRrRan

Hey There that second formula should have read like this

=if(or(J3="",N3="",R3="",V3="",Z3="",AD3="",AH3="" ,AL3="",AP3="",AT3="",AX3="",BB3=""),"",if((J3+N3+ R3+V3+Z3+ad3+ah3+al3+ap3+at3+ax3+bb3)/
COUNTA(J3,N3,R3,V3,Z3,ad3,ah3,al3,ap3,at3,ax3,bb3) ,
"no settlements"))

I forgot to look at it closely before I posted above


Oops

Chris


---
Message posted from http://www.ExcelForum.com/

christopherp wrote in
:

Hey There that second formula should have read like this

=if(or(J3="",N3="",R3="",V3="",Z3="",AD3="",AH3="" ,AL3="",AP3="",AT
3="",AX3="",BB3=""),"",if((J3+N3+R3+V3+Z3+ad3+ah3+ al3+ap3+at3+ax3+b
b3)/ COUNTA(J3,N3,R3,V3,Z3,ad3,ah3,al3,ap3,at3,ax3,bb3) ,
"no settlements"))

I forgot to look at it closely before I posted above


Oops

Chris


---
Message posted from http://www.ExcelForum.com/


But it still doesn't provide a logical test for the 2nd IF-function.
Here's what you are typing:

=IF(logical_test1,"",IF(logical_test2,"no
settlements",value_if_FALSE))

I am missing two parts of this formula:
1. logical_test2 ISN'T a logical test, it just shows you your average
2. "value_if_FALSE" for the 2nd IF-function is missing (not required
though; if false, the cell will show "FALSE")

So, does my formula in my previous post help you with your problem,
or do you want to formula to be written in the shape of your last
formula?

Please elaborate,
CoRrRan

=IF(isblank(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn 3),"",(K3+P3+U3+Z3+Ae3+Aj3+Ao3+At3+Ay3+bd3+Bn3)/
COUNTA(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3))

If any of the cells in the isblank formula are blank I wish the
returned value to be blank, hence the "" after the first) bracket.

If any the cells do contain a value, I wish the resulted of the second
for be displayed ---- i.e:

(K3+P3+U3+Z3+Ae3+Aj3+Ao3+At3+Ay3+bd3+Bn3)/
COUNTA(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3))

I hope this helps clarify what I am trying to do

Chris


---
Message posted from http://www.ExcelForum.com/

christopherp wrote...
This is the entire formula I have now

=IF(isblank(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,B n3),"",
(K3+P3+U3+Z3+Ae3+Aj3+Ao3+At3+Ay3+bd3+Bn3)/
COUNTA(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3) )

If any of the cells in the first part of the formula are blank I wish
the resulting value to be blank, hence the ""
...
If any the cells are not blank, I wish the result of the second
part of the formula below to be displayed
...

You could try

=IF(COUNTA((K3,P3,U3,Z3,AE3,AJ3,AO3,AT3,AY3,BD3,BN 3))=11,
AVERAGEA((K3,P3,U3,Z3,AE3,AJ3,AO3,AT3,AY3,BD3,BN3) ),"")

which would treat any nonnumeric text in these cells as zeros, or you
could replace AVERAGEA with AVERAGE to simply skip such cells, or if
you want to see the #VALUE! errors that your formula would give if all
cells contained something but some contained nonnumeric text, try

=IF(COUNTA((K3,P3,U3,Z3,AE3,AJ3,AO3,AT3,AY3,BD3,BN 3))=11,
(K3+P3+U3+Z3+AE3+AJ3+AO3+AT3+AY3+BD3+BN3)/11,"")


---
Message posted from http://www.ExcelForum.com/