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

[christopherp]

Hi,

I am trying to work out the average loan size over a given number o
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 formul
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 wron
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),"n
settlements"))

Any help would be greatly appreciated.

Chris :confused

 

[CoRrRan]

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

Would this do the trick:

=IF(ISERROR((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));"";(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

 

[christopherp]

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

Chri

 

[CoRrRan]

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

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

 

[christopherp]

=IF(isblank(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3),"",(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 th
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 secon
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

Chri

 

[hgrove]

christopherp wrote...

This is the entire formula I have now

=IF(isblank(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3),"",
(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,BN3))=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 yo
could replace AVERAGEA with AVERAGE to simply skip such cells, or i
you want to see the #VALUE! errors that your formula would give if al
cells contained something but some contained nonnumeric text, try

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