Cumulative day count in list

[F.H. van Zelm]

Hi guys,

To be honest: for now I lack the spirit to solve my problem by myself.
And it must have been done before so I just ask for help. I'm a lazy
bastard ;-(.

In Excel 2003, a list with items in column B.
In columns C, D etc. in row one are dates. E.g. 01/01/08, 02/01/08,
02/29/08 etc ending with today. In future, more dates will be added.
The intersection of an item row and a date column has an 'x' when
the item is 'on' from that specific date to the next.
In column A, I calculate the total days an item has been on. The
formula reads:
=IF(C2="x",D$1-C$1,0)+IF(D2="x",E$1-D$1,0)+IF(E2="x",F$1-E$1,0)

So it looks like this:
A B C D E F
1 01/01 02/01 02/29 =today()
2 59 item1 x x
3 31 item2 x x

The formula works all right for a few columns. By December, the
formula will be unreadable. In four years it will exceed the 1024-
character limit. In short: this is ugly!
It would be great to compress all IF's to one single IF. I think it
tends to an array formula ... But how?

Or is there some other different and pritty solution?

 

[Pete_UK]

Will you always have 2 x's in each row (eg in row 3 in your example,
do you only put an x in column D when you know which date to put in
column E), or might you have a starting x without a stop x ?

Are the x's always in adjacent columns? You are not likely to start
something before finishing an earlier task?

The number of days in your example is not accurate, if I understand
what you are trying to do.

Pete

 

[Frans]

Hi Pete,

Thanks for reply. There seems to be something wrong with my Outlook
account
so I didn't see your answer before. Now I'll try Google mail ...

To answer your questions:
- the 'x' indicated 'on line from date above to the next'.
- so there can be any combinations of 'x's.
- perhaps using =TODAY() caused the difference. The values look
correct to me.

See my 'self response' ;-) for the solution.

Frans

 

[Frans]

Hi guys,

'Lazy' but not completly ignorant. As there were no answers, I gave it
a try myself. The next formula is not yet fully flexible but it works:

{=SUM(IF(C2:E5="x";$D$1:$F$1-$C$1:$E$1))}

If placed in A2, it can be copied down to next rows.

I love / hate array formulas and finding it myself gave some self-
respect.

Mvg, Frans
www.fhvzelm.com

 

[Frans]

Once again ...

I think, I found a fully flexible solution. Flexible in the sense that
you can
add new columns with new dates and keeping the cumulitive date formula
working.

- Give a name to the last date cell: LastDate
- Name the column with that last date cell LastColumn
- Then use the formula below to calculate the days
{=SUM(IF(D8:INDIRECT(CHAR(Column(LastColumn)+63)&ROW(B8))="x";$E
$4:LastDate-$D$4:INDIRECT(CHAR(COLUMN(LaatsteDatum)
+63)&ROW(LastDate))))}
A working spreadsheet can be found on www.fhvzelm.com in
Example Files-DiversenBerekeningen.xls, sheet Matrices
on row 80.

CU, Frans