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?

--
Mvg, Frans
www.fhvzelm.com

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

On Mar 3, 10:18*am, "F.H. van Zelm" <fhvz...@hotmail.com> wrote:
> 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?
>
> --
> Mvg, Franswww.fhvzelm.com

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

On 3 mrt, 12:20, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Mar 3, 10:18*am, "F.H. van Zelm" <fhvz...@hotmail.com> wrote:
>
>
>
> > 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?
>
> > --
> > Mvg, Franswww.fhvzelm.com- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

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

On 3 mrt, 11:18, "F.H. van Zelm" <fhvz...@hotmail.com> wrote:
> 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?
>
> --
> Mvg, Franswww.fhvzelm.com

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

On 13 mrt, 23:25, Frans <fhvz...@gmail.com> wrote:
> 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, Franswww.fhvzelm.com
>
> On 3 mrt, 11:18, "F.H. van Zelm" <fhvz...@hotmail.com> wrote:
>
>
>
> > 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?
>
> > --
> > Mvg, Franswww.fhvzelm.com- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -