Basing a formula on an unknown number of rows

**LilyB** · 10-23-2012, 03:55 PM

I'm not sure if this can even be done, but I've certainly had a lot of trouble working it out.

Simply, I have a column with time, and another for which I have marked a 1 saying something is happening (this is based on a large number of pictures I have collected for my PhD) or empty for no activity. I am trying to calculate the length of time that there are consecutive pictures, which is easy enough on its own but I was wondering if there was a formula I could use to save me a bit of time.

Basically; if a cell contains a 1 then count the number of cells above it which contain a value and use that to work out the time from that column.

It feels like something which can be done but maybe its just wishful thinking and I certainly can't work it out.

Thanks very much
Lily

**dilipandey** · 10-24-2012, 02:21 AM

Hi Lily,

Welcome to the forum.

Basically; if a cell contains a 1 then count the number of cells above it which contain a value and use that to work out the time from that column.

Can you post a sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**LilyB** · 10-24-2012, 01:11 PM

HELP.docx

I'm sorry, I don't know if that worked?

Hopefully you can see what I mean, I've highlighted the relevant columns as there's quite a lot going on.

Thanks
Lily