Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I exclude cells from a formula? | Excel Discussion (Misc queries) | |||
Median array formula | Excel Worksheet Functions | |||
Is there a way that I can exclude a formula from being printed? | Excel Discussion (Misc queries) | |||
Median Formula In Pivot Table | Excel Worksheet Functions | |||
Excel 2003 - SUM Formula - exclude cell | Excel Worksheet Functions |