#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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!
|


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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
|
|


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Formula trouble

Perfect. Thank you very much for your time!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trouble with my formula Excluxe Excel Discussion (Misc queries) 3 July 17th 07 06:43 PM
'IF' formula trouble agw3 Excel Discussion (Misc queries) 2 August 9th 06 04:16 PM
Trouble with an IF formula glenn Excel Worksheet Functions 1 July 27th 06 07:31 PM
I'm new at this - having trouble with a formula Dave Excel Discussion (Misc queries) 3 January 8th 06 03:16 AM
Trouble with formula Kristajea Excel Worksheet Functions 4 May 20th 05 12:28 AM


All times are GMT +1. The time now is 07:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"