Counting problem

[Nuno D]

Hi!


I need a little help on this counting problem

I've tried but I haven't been able to solve this one

I have several rows. I have dates(03-01-2003, 03-02-2003, etc.) in the
first one.
On the other ones I have blank cells and cells with numbers.

What I need is a formula that counts the number of blank cells
counting from the last cell with a number to TODAY()

Example

Row 1 03-01-2003 03-02-2003 03-03-2003 03-04-200 03-04-2003
Row two 127

If today equaled 03-04-2004

The answer with be 3

Thanks for any help

 

[Jason Morin]

Try:

=MAX(IF(A1:L1=TODAY(),COLUMN(A1:L1)))-MAX(IF(ISNUMBER(A2:L2
),COLUMN(A2:L2)))

Press ctrl/shift/enter for this to work. Adjust the ranges
if needed.

HTH
Jason
Atlanta, GA

 

[Leo Heuser]

Hi Nuno

Another way:

Assuming dates in row 1 and numbers in row 2 enter
this array-formula:

=MATCH(TODAY(),1:1,0)-MAX((2:2<>"")*COLUMN(A:IV))

must be entered with <Shift><Ctrl><Enter> also if edited later.

Notice, that upon entering, COLUMN(A:IV)
becomes COLUMN($1:$65536)

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[Nuno D]

Thanks Jason, your formula worked out fine except for one small thing.
I'm using four sheets one for each trimester.
You formula will not consider the days and numbers that are on the
other sheets.
Is there a way to do this?

I need to have a column for each day of the year that's why I was
forced to put the data in to four sheets.

Once again thanks for any help

 

[Nuno D]

Hi Leo

I tried using you formula but it didn't work.
The numbers and dates that I'm using don't start from the first
column.
Anyway I've encountered some more difficulties. I need to use four
sheets for my data.
Check my post to Jason to see watt I mean.

Nevertheless thanks for your help.