Ask Your Question

In Calc, how to find the distance to the nearest nonblank cell? [closed]

asked 2014-08-03 03:02:32 +0200

Polyergic gravatar image

updated 2014-08-03 03:03:46 +0200

Specifically, I want the number of blank cells above the current cell. I'm using a sheet as a log, and I want to leave a row blank when I miss a day, but be able to count the number of consecutive missed days. I use the missed days count to calculate the effective per-day averages on the next day that isn't missed.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-01 21:16:26.562279

2 Answers

Sort by » oldest newest most voted

answered 2014-08-03 09:08:33 +0200

karolus gravatar image

Bad Idea !!

Log with the real Date, and without blanc rows for "missing Days" !


edit flag offensive delete link more


I'm agree with @karolus, blank records can only be a source of troubles (e.g. not possible sort data). To know how many missed days were, is the difference between consecutive logs date.

m.a.riosv gravatar imagem.a.riosv ( 2014-08-03 14:19:08 +0200 )edit

answered 2014-08-03 14:51:30 +0200

lactea gravatar image

Maybe he has no choice but to use this spreadsheet.

I'd use either countblank() or if(cell="";1;0) and then count all the 1's before or after a filled date. Simply another if()-function. Makes the document slow, though.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-08-03 03:02:32 +0200

Seen: 306 times

Last updated: Aug 03 '14