Formula trouble
 

Hiya

I've been trying to figure a problem out for the last couple of
days...

I'm working on a sheet that takes percentages and averages them into
totals.

Those totals are then averaged into a final total.

The formula for the final total so far is =((IF(ISBLANK(J14),
0,J14)*FTE!$E$8)+(IF(ISBLANK(K14),0,K14)*FTE!$E$9) +(IF(ISBLANK(V14),
0,V14)*FTE!$E$10)+(IF(ISBLANK(W14),0,W14)*FTE!$E$1 1)+(IF(ISBLANK(X14),
0,X14)*FTE!$E$12)+(IF(ISBLANK(AA14),0,AA14)*FTE!$E $13))/
((IF(ISBLANK(J14),0,FTE!$E$8))+(IF(ISBLANK(K14),0, FTE!$E$9))+
(IF(ISBLANK(V14),0,FTE!$E$10))+(IF(ISBLANK(W14),0, FTE!$E$11))+
(IF(ISBLANK(X14),0,FTE!$E$12))+(IF(ISBLANK(AA14),0 ,FTE!$E$13)))

This works ok but the problem is, if a cell is 'blank' due to using a
formula such as =IF(ISERROR(AVERAGE(Y14:Z14)),"",(AVERAGE(Y14:Z14) )),
it shows an error. It seems that because of the formula, the result
isn't truely blank even though it shows no figure.

The sheet needs to have some blank cells or 0-100% so I can't see a
way round this.

Can try to explain it a bit better if this is too confusing!

Formula trouble
 

Instead of ISBLANK, use =""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"AndyR" wrote in message ps.com...
| Hiya
|
| I've been trying to figure a problem out for the last couple of
| days...
|
| I'm working on a sheet that takes percentages and averages them into
| totals.
|
| Those totals are then averaged into a final total.
|
| The formula for the final total so far is =((IF(ISBLANK(J14),
| 0,J14)*FTE!$E$8)+(IF(ISBLANK(K14),0,K14)*FTE!$E$9) +(IF(ISBLANK(V14),
| 0,V14)*FTE!$E$10)+(IF(ISBLANK(W14),0,W14)*FTE!$E$1 1)+(IF(ISBLANK(X14),
| 0,X14)*FTE!$E$12)+(IF(ISBLANK(AA14),0,AA14)*FTE!$E $13))/
| ((IF(ISBLANK(J14),0,FTE!$E$8))+(IF(ISBLANK(K14),0, FTE!$E$9))+
| (IF(ISBLANK(V14),0,FTE!$E$10))+(IF(ISBLANK(W14),0, FTE!$E$11))+
| (IF(ISBLANK(X14),0,FTE!$E$12))+(IF(ISBLANK(AA14),0 ,FTE!$E$13)))
|
| This works ok but the problem is, if a cell is 'blank' due to using a
| formula such as =IF(ISERROR(AVERAGE(Y14:Z14)),"",(AVERAGE(Y14:Z14) )),
| it shows an error. It seems that because of the formula, the result
| isn't truely blank even though it shows no figure.
|
| The sheet needs to have some blank cells or 0-100% so I can't see a
| way round this.
|
| Can try to explain it a bit better if this is too confusing!
|

Formula trouble
 

I see the idea behind it but not sure where to slot it in exactly. The
way I read it is below but obviously doesn't work.

=((IF(=""(J14),0,J14)*FTE!$E$8)+(IF(=""(K14),0,K14 )*FTE!$E$9)+
(IF(=""(V14),0,V14)*FTE!$E$10)+(IF(=""(W14),0,W14) *FTE!$E$11)+
(IF(=""(X14),0,X14)*FTE!$E$12)+(IF(=""(AA14),0,AA1 4)*FTE!$E$13))/
((IF(=""(J14),0,FTE!$E$8))+(IF(=""(K14),0,FTE!$E$9 ))+(IF(=""(V14),
0,FTE!$E$10))+(IF(=""(W14),0,FTE!$E$11))+(IF(=""(X 14),0,FTE!$E$12))+
(IF(=""(AA14),0,FTE!$E$13)))



On Oct 24, 12:27 pm, "Niek Otten" wrote:
Instead of ISBLANK, use =""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Formula trouble
 

IF(J14="",0,J14) etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"AndyR" wrote in message s.com...
|I see the idea behind it but not sure where to slot it in exactly. The
| way I read it is below but obviously doesn't work.
|
| =((IF(=""(J14),0,J14)*FTE!$E$8)+(IF(=""(K14),0,K14 )*FTE!$E$9)+
| (IF(=""(V14),0,V14)*FTE!$E$10)+(IF(=""(W14),0,W14) *FTE!$E$11)+
| (IF(=""(X14),0,X14)*FTE!$E$12)+(IF(=""(AA14),0,AA1 4)*FTE!$E$13))/
| ((IF(=""(J14),0,FTE!$E$8))+(IF(=""(K14),0,FTE!$E$9 ))+(IF(=""(V14),
| 0,FTE!$E$10))+(IF(=""(W14),0,FTE!$E$11))+(IF(=""(X 14),0,FTE!$E$12))+
| (IF(=""(AA14),0,FTE!$E$13)))
|
|
|
| On Oct 24, 12:27 pm, "Niek Otten" wrote:
| Instead of ISBLANK, use =""
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|

Formula trouble
 

Perfect. Thank you very much for your time!