| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match name and Date Period for %
In sheet 1
Name Date % u 31-Mar-07 3.00% P 30-Mar-07 1.20% w 06-May-07 4.50% w 10-Jun-07 #N/A In Sheet 2 Name % Start End p 1.2% 01-Jan-07 31-Mar-07 u 3.0% 09-Feb-07 06-May-07 w 4.5% 06-May-07 08-Jun-07 =INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A1=Sheet2!$A$2:$A $4)*(B1=Sheet2!$C$2:$C$4)*(B1<=Sheet2!$D$2:$D$4), 0)) It is found that if the date specified is out of the range of Sheet 2, we got the result "#N/A". How could I change it to zero instead of "#N/A" for my further calculation. Thanks in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match name and Date Period for %
Try:-
IF(ISERROR(INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A2=Shee t2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<=Sheet2!$ D$2:$D$4),0))),"0",INDEX(Sheet2!$B$2:$B$4,MATCH(1, (A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<= Sheet2!$D$2:$D$4),0))) Mike "aggregate sum over the periods" wrote: In sheet 1 Name Date % u 31-Mar-07 3.00% P 30-Mar-07 1.20% w 06-May-07 4.50% w 10-Jun-07 #N/A In Sheet 2 Name % Start End p 1.2% 01-Jan-07 31-Mar-07 u 3.0% 09-Feb-07 06-May-07 w 4.5% 06-May-07 08-Jun-07 =INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A1=Sheet2!$A$2:$A $4)*(B1=Sheet2!$C$2:$C$4)*(B1<=Sheet2!$D$2:$D$4), 0)) It is found that if the date specified is out of the range of Sheet 2, we got the result "#N/A". How could I change it to zero instead of "#N/A" for my further calculation. Thanks in advance |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match name and Date Period for %
Thanks. I got the result .
"Mike H" wrote: Try:- IF(ISERROR(INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A2=Shee t2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<=Sheet2!$ D$2:$D$4),0))),"0",INDEX(Sheet2!$B$2:$B$4,MATCH(1, (A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<= Sheet2!$D$2:$D$4),0))) Mike "aggregate sum over the periods" wrote: In sheet 1 Name Date % u 31-Mar-07 3.00% P 30-Mar-07 1.20% w 06-May-07 4.50% w 10-Jun-07 #N/A In Sheet 2 Name % Start End p 1.2% 01-Jan-07 31-Mar-07 u 3.0% 09-Feb-07 06-May-07 w 4.5% 06-May-07 08-Jun-07 =INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A1=Sheet2!$A$2:$A $4)*(B1=Sheet2!$C$2:$C$4)*(B1<=Sheet2!$D$2:$D$4), 0)) It is found that if the date specified is out of the range of Sheet 2, we got the result "#N/A". How could I change it to zero instead of "#N/A" for my further calculation. Thanks in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I match a calculated date to closest date from a list? | Excel Worksheet Functions | |||
| Sum column based on value in each row, if two cells equal, or if date is within time period | Excel Worksheet Functions | |||
| Every time i put a number with period it becomes a date and time | Excel Discussion (Misc queries) | |||
| Period to Period percentage change? | Excel Discussion (Misc queries) | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Linear Mode
