Formula trouble

A

AndyR

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$11)+(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!
 
N

Niek Otten

Instead of ISBLANK, use =""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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$11)+(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!
|
 
A

AndyR

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,AA14)*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(=""(X14),0,FTE!$E$12))+
(IF(=""(AA14),0,FTE!$E$13)))
 
N

Niek Otten

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|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,AA14)*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(=""(X14),0,FTE!$E$12))+
| (IF(=""(AA14),0,FTE!$E$13)))
|
|
|
| > Instead of ISBLANK, use =""
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Chart SERIES OFFSET 5
Pivot Table help 0
type mismatch error in macro in excel 0
League standings based off final scores 5
Working day(s)/Time between two dats (No Weekend or Holidays) 0
if(and(isblank( formula help 2
Complex formula help 4
Averaging blank or cells with Zeros in them. 4

Top