Exclude Sat and Sun, Median Formula
 

Hi,

I have this formula: =MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-1)*$F4))

Column E = Total Hours
Column F = Hours per Day

It helps me to distribute the amount of hours per day. So if column E has a
value of 40 Hrs

Colums G equals to 16
Column H equals to 16
Column I equals to 8
Column J equals to 0
and so on.........

Works great. But What I would like to do is to some how check the day of the
week, if my column is sat or sun the value will be zero.
The problem I have is that the next Column which will be Monday return a
value of Zero. Making the formula useless.

Any help will be really appreciated.

Regards,

Bre-x

Exclude Sat and Sun, Median Formula
 

Assuming you have dates in row 3:

=IF(WEEKDAY(G3,2)5,0,MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-SUMPRODUCT(--(WEEKDAY($G3:G3,2)5))-1)*$F4)))

--
Biff
Microsoft Excel MVP


"Bre-x" wrote in message
...
Hi,

I have this formula: =MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-1)*$F4))

Column E = Total Hours
Column F = Hours per Day

It helps me to distribute the amount of hours per day. So if column E has
a value of 40 Hrs

Colums G equals to 16
Column H equals to 16
Column I equals to 8
Column J equals to 0
and so on.........

Works great. But What I would like to do is to some how check the day of
the week, if my column is sat or sun the value will be zero.
The problem I have is that the next Column which will be Monday return a
value of Zero. Making the formula useless.

Any help will be really appreciated.

Regards,

Bre-x

Exclude Sat and Sun, Median Formula
 

We can reduce that a little. Instead of subtracting weekends we can just
count the weekdays:

=IF(WEEKDAY(G3,2)5,0,MEDIAN(0,$F4,$E4-(SUMPRODUCT(--(WEEKDAY($G3:G3,2)<6))-1)*$F4))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming you have dates in row 3:

=IF(WEEKDAY(G3,2)5,0,MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-SUMPRODUCT(--(WEEKDAY($G3:G3,2)5))-1)*$F4)))

--
Biff
Microsoft Excel MVP


"Bre-x" wrote in message
...
Hi,

I have this formula: =MEDIAN(0,$F4,$E4-((COLUMNS($G4:G4)-1)*$F4))

Column E = Total Hours
Column F = Hours per Day

It helps me to distribute the amount of hours per day. So if column E has
a value of 40 Hrs

Colums G equals to 16
Column H equals to 16
Column I equals to 8
Column J equals to 0
and so on.........

Works great. But What I would like to do is to some how check the day of
the week, if my column is sat or sun the value will be zero.
The problem I have is that the next Column which will be Monday return a
value of Zero. Making the formula useless.

Any help will be really appreciated.

Regards,

Bre-x