Hi! I have a worksheet with several tables. The tables are linked to each other using functions such as LOOKFORROW and others. I want to calculate the average of certain columns. however in many columns there is one or more cells that says #MISSING!, hence I cannot calculate an average. How do I solve this? I want simply ignore the missing values or treat them as the value befor or something. thanks alot for any help
=AVERAGE(IF(ISNUMBER(A1:A100),A1:A100,"")) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200807 "Arne Hegefors" wrote: > Hi! I have a worksheet with several tables. The tables are linked to each > other using functions such as LOOKFORROW and others. I want to calculate the > average of certain columns. however in many columns there is one or more > cells that says #MISSING!, hence I cannot calculate an average. How do I > solve this? I want simply ignore the missing values or treat them as the > value befor or something. thanks alot for any help
In Swedish that would be =MEDEL(OM(ÄRTAL(A1:A100);A1:A100;"")) still entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Gary''s Student" wrote in message news:6A773135-8535-4486-8BC7-DDA41A51320F@microsoft.com... > =AVERAGE(IF(ISNUMBER(A1:A100),A1:A100,"")) > > This is an array formula that must be entered with CNTRL-SHFT-ENTER > rather than just the ENTER key. > -- > Gary''s Student - gsnu200807 > > > "Arne Hegefors" wrote: > >> Hi! I have a worksheet with several tables. The tables are linked to each >> other using functions such as LOOKFORROW and others. I want to calculate >> the >> average of certain columns. however in many columns there is one or more >> cells that says #MISSING!, hence I cannot calculate an average. How do I >> solve this? I want simply ignore the missing values or treat them as the >> value befor or something. thanks alot for any help
