Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with calculations
I have run into two problems with the following calculations, mainly
because I did not put enough thought into the processes. First, is there away of making the elapsed days go from a live number to a historical number once AR2 = "closed"? Second, is it possible if D2 = "N/a" then it does not calculate and returns "Not Required"? Days Elapsed Calculation: =NETWORKDAYS(U2,TODAY(),Holidays!B7:B14)-1 On the following days remaining calculation, it appears to be working fine, but once again I did not put enough thought into it. First, I cannot seem to get the "Not Required" to show if AA2 displays anything other than St1, ECC St1, St2, St3, PE or FOI. As per above, it would help if U2 or Z2 = "N/a" it returned "Not Required". If AR2= "closed" it should then return "Not Required" Days Remaining Calculation: =NETWORKDAYS(TODAY(),IF(AA2="","", IF(OR(AA2="ST1",AA2="PE",AA2="ECC ST1"),WORKDAY(U2,10,Holidays!B7:B14), IF(OR(AA2="CA - ST2",AA2="FOI"),WORKDAY(Z2,20,Holidays!B7:B14), IF(AA2="ST3",WORKDAY(Z2,28,Holidays!B7:B14),"Not Required")))),Holidays!B7:B14)-1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with calculations
"Scoooter" wrote in message ps.com... I have run into two problems with the following calculations, mainly because I did not put enough thought into the processes. First, is there away of making the elapsed days go from a live number to a historical number once AR2 = "closed"? Second, is it possible if D2 = "N/a" then it does not calculate and returns "Not Required"? Days Elapsed Calculation: =NETWORKDAYS(U2,TODAY(),Holidays!B7:B14)-1 Do you mean? =ID(D2="N/A","Not Required", NETWORKDAYS(U2,IF(AR2="Closed",Z2,TODAY()),Holiday s!B7:B14)-1) On the following days remaining calculation, it appears to be working fine, but once again I did not put enough thought into it. First, I cannot seem to get the "Not Required" to show if AA2 displays anything other than St1, ECC St1, St2, St3, PE or FOI. As per above, it would help if U2 or Z2 = "N/a" it returned "Not Required". If AR2= "closed" it should then return "Not Required" Days Remaining Calculation: =NETWORKDAYS(TODAY(),IF(AA2="","", IF(OR(AA2="ST1",AA2="PE",AA2="ECC ST1"),WORKDAY(U2,10,Holidays!B7:B14), IF(OR(AA2="CA - ST2",AA2="FOI"),WORKDAY(Z2,20,Holidays!B7:B14), IF(AA2="ST3",WORKDAY(Z2,28,Holidays!B7:B14),"Not Required")))),Holidays!B7:B14)-1 =IF(OR(NOT(ISNUMBER(MATCH(AA2,{"ST1","PE","ECC ST1","CA - ST2","FOI","ST3"},0))), U2="N/A",Z2="N/A",AR2="Closed"),"Not Required", NETWORKDAYS(TODAY(),IF(AA2="","", IF(OR(AA2="ST1",AA2="PE",AA2="ECC ST1"),WORKDAY(U2,10,Holidays!B7:B14), IF(OR(AA2="CA - ST2",AA2="FOI"),WORKDAY(Z2,20,Holidays!B7:B14), IF(AA2="ST3",WORKDAY(Z2,28,Holidays!B7:B14),"Not Required")))),Holidays!B7:B14)-1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations in French - Clarification | Excel Discussion (Misc queries) | |||
complex calculations | Excel Discussion (Misc queries) | |||
Calculations based on PivotTable information | Excel Discussion (Misc queries) | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) |