create table of reading, problem when i have only half values half solved

**j_r_m_c** · 02-10-2011, 05:20 PM

Hello,

i´m building a page to colect some readings
it works well, with a exception.
if i have only half readings i will affect the others values

please try in my example attached

**NBVC** · 02-10-2011, 05:44 PM

How about adjust the COUNT() ranges in the INDEX(K$13:AN$13...) functions to count the current row entries..

e.g. in I14:

Please Login or Register  to view this content.

and in J14:

Please Login or Register  to view this content.

both copied down.

**j_r_m_c** · 02-10-2011, 07:37 PM

thank you
i understand what you mean,
good call

after updated the formulas, i found another problem

if i miss a value in one reading, in the next reading it will not be consider in calculation, because the open space in the reading.
if i put a letter the function wont count,and give me error
if a put a 0 (zero) it will affect the rest of the calculations

any sugestions??

**NBVC** · 02-10-2011, 10:32 PM

I replaced all you COUNT(K14:AN14) functions with MATCH(9.99E+307,K14:AN14).

The COUNT(K14:AN14) function counts number of cells in range that have a number in them.. this is why it was skewed when you had blank cells.

The MATCH(9.99E+307,K14:AN14) function matches cell containing last numeric entry since this function is looking for a very large number (9.99e+307) in the range, and returning the last cell with a number less than that large number...which is always the last cell in the range with a number...