Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula trouble
Perfect. Thank you very much for your time!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with my formula | Excel Discussion (Misc queries) | |||
'IF' formula trouble | Excel Discussion (Misc queries) | |||
Trouble with an IF formula | Excel Worksheet Functions | |||
I'm new at this - having trouble with a formula | Excel Discussion (Misc queries) | |||
Trouble with formula | Excel Worksheet Functions |