Trying to make a fromula conditional on at least one cell containg a value (not blank

[christopherp]

Hey there,

I am trying to work out the average loan size from a number o
different referral sources.

The formula started out like this:

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

This did what I need but the problem with this was if any of the liste
cells were blank it would return a #DIV/0! error which wasn'
satisfactory. I have amended the formula and it now looks like:

=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 wis
the resulting value to be blank, hence the ""

i.e

isblank(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3),"",


If any the cells are not blank, I wish the result of the second part o
the formula below to 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 just know there is an easy way to do what i need to do but the logi
escapes me at the moment. Thinking about I am not sure if isblank i
the best way to go because I would like the second part of the formul
to run as long as one or more of the cells is not blank. Oh I don'
know i am confused. --

Any help would be greatly appreciated

Chri

 

[Frank Kabel]

Hi
try:
=IF(counta(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3)=0,"",
(K3+P3+U3+Z3+Ae3+Aj3+Ao3+At3+Ay3+bd3+Bn3)/
COUNTA(K3,P3,U3,Z3,Ae3,Aj3,Ao3,At3,Ay3,bd3,Bn3))