Posts by Paddyd

Paddyd · Apr 23rd 2003

there's nothing in d2 on any of the sheets - what condition are you trying to apply?

As an aside, for numeric data, a simple, generalisable metrhod for conditional computation is as follows:

Conditional counting:

=sumproduct((range1=condition1)*(range2=condition2)*...*(rangeN=conditionN))

Conditional summing:

=sumproduct((range1=condition1)*(range2=condition2)*...*(rangeN=conditionN)*(sumrange))

paddy

Paddyd · Apr 22nd 2003

Hi.

In general, the way to get a formula to change it's refernece to reflect the addition of new data is to use a dynamic range as the cell reference. It is easy to set one up tht, for example, will always refer to the last 4 rows of data in a column.

So, for example, given the way you've got your data set uyp, the following will return the average for the last 4 entries on your sheet:

=AVERAGE(INDEX(Happ!D3:D59,COUNTA(Happ!D3:D59)-3,1):INDEX(Happ!D3:D59,COUNTA(Happ!D3:D59),1))

...not including the summary line you've got on row 60 of the data sheets.

If you could change the tab names so that they're all the same as the 'names' you've got on the summary stats sheet - it would make the final formulas easier to write (or at least to copy down...):

=AVERAGE(INDEX(INDIRECT(A4&"!D3:D59"),COUNTA(INDIRECT(A4&"!D3:D59"))-3,1):INDEX(INDIRECT(A4&"!D3:D59"),COUNTA(INDIRECT(A4&"!D3:D59")),1))

although all the indirects() might start playing with your file's performance.

paddy

Paddyd · Apr 22nd 2003

Hi - welcome to the board!

Do you mean "Can I have the contents of one drop-down / filter list based on the results of another'? If so, then apply the following method (only one of many available):

"The method is as follows:

Enter in some column what follows:

{"USA";"FRANCE"}[ That's, enter USA in a cell, then FRANCE in the next cell down ]

Select these cells, go to the Name Box on the Formula Bar, and type COUNTRIES followed by enter.

Enter in a column next to COUNTRIES:

{"New York";"Pittsburgh";"Los Angeles";"Boston"}

Name this range of cells USA via the Name Box as described above.

Enter in a column next to USA:

{"Paris";"Nice";"Toulon"}

Name this range FRANCE.

Just to see how this works,

activate A1 in some worksheet in the same workbook;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=COUNTRIES

click OK;

activate another cell in the same worksheet, say, C1;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=INDIRECT(A1)

click OK.

Now you have two lists of which the 2nd depends on the selection from the 1st. "

....the quote is from Aladin Akyurek. You should be able to generalise it to other situations.

Paddyd · Apr 17th 2003

a pivot table would be the simplest solution to adding up the hours by product.

Paddyd · Apr 16th 2003

also, a standard answer to the question "I want to do things with data label, ebut excel's charting options are too limited" is often "Download Rob Bovey's ChartLabeler Addin":

http://www.appspro.com/utilities/Labeler.asp

Paddyd · Apr 16th 2003

Hi - welcome to the board!

Kind of depends what you mean by 'label', what type of graph you're working with etc. post back with more details & the answer will emerge well before you starve.

paddy

Paddyd · Apr 14th 2003

Hi,

It is a "feature" of pivot tables to retain the classings. The usual response is that you have to re-create the pivot table. There is, however, a quicker work-around:

1) in the source data, rename the field that has changed. hit refresh
2) change it back to the original name, hit refresh.
3) pull the field back into te pivot table.

...not elegant, but qicker than building the whole thing again.

paddy

Paddyd · Apr 9th 2003

Andy,

Just a (no doubt unnecessary) note of caution for the OP re your suggested:

=IF(ISERROR(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193)),0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

iserror() will mask all errors, including syntactical ones in the formula construction. In geeneral, error checks are best made as specific as possible:

if(error.type(AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))=2,0,AVERAGE(G6,M6,S6,AE6,AK6,G100,M100,S100,Y100,AE100,AK100,G193))

paddy

Paddyd · Apr 9th 2003

Hi - welcome to the board!

I took a look at the attachment. The answer to your question "Is my whole calculating basically flawed from the start?" is yes (from a design point of biew) - although if it serves it's general purpose I guess it's OK.

As you have discovered, the way you have set up your data is giving you headaches. Here is not the place to go into the elements of good spreadsheet design, but the folliowing links might prove of interest:

http://spreadsheetstyle.com/

and

http://bsstudents.uce.ac.uk/le…ourse%20Material/smbp.pdf

although the latter is a rather large document. also, do a search on the web for "first normal form", read some of the articles & see how it might affect your design descisions.

more specifically, you are not getting div/0 errors because of the NN's and NA's. dividing a number by text values returns the #value! error. You're getting them because you're dividing by zero.

One way to get round this is to check for this condition before you perform the calculations:

rather than

=AVERAGE(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221)

try:

=if(sum(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221)=0,"Whatever",AVERAGE(G34,M34,S34,AE34,AK34,G128,M128,S128,Y128,AE128,AK128,G221))

...substitute "something" with whatever you want returned instead of div/0.

There are other alternatives, but given the 'complexities' of your spreadsheet design, there probably not worth discussiong until we know whether you intend to re-consider the way you're data is set up.

HTH

paddy

Paddyd · Apr 7th 2003

butt in all you like - the more the merrier!

Paddyd · Apr 7th 2003

vlookup()'s what you need. check it out in the help file & see example below:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book5</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb724366><INPUT onclick='window.clipboardData.setData("Text",document.formFb049879.sltNb288580.value);' type=button value="Copy Formula" name=btCb259793></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6><TABLE border=0><TBODY><TR><FORM name=formFb049879><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288580.value" name=sltNb288580><OPTION value==VLOOKUP(E1,A1:B5,2,0) selected>E2</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value==VLOOKUP(E1,A1:B5,2,0) name=txbFb195777></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Ref</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Value</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Lookup</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Car</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Airplane</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">            1 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Return</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"><A onclick="document.formFb049879.sltNb288580.options[0].selected=true; document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288580.value;" href="#javascript:void(0);">3</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Baloon</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">            2 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Car</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">            3 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Boat</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">            4 </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

the formula is

=VLOOKUP(E1,A1:B5,2,0)

HTH

paddy

Paddyd · Apr 7th 2003

Hi - welcome to the board!

"will change text to an assigned value depending on what text it is "

...care to post back with the details about what you are actually trying to do - examples really help!

paddy

Paddyd · Apr 7th 2003

Hi - welcome to the board.

see the help file for:

'Sort data in a PivotTable or PivotChart report'

...it is possible to define a custom sort order for your pivot table.

Paddyd · Apr 6th 2003

"I was doing my usual referencing on a dynamic range from another sheet this week and I swear to God the ROW formula changed each time I looked at it in insert/names/define..... it was driving me nuts"

Chris,

You more-or-less always need to use absolute references in named formulas - is that what you mean?

paddy

Paddyd · Apr 6th 2003

=sheet1!a1-sheet2!a1

...is that it?

Paddyd · Apr 6th 2003

via U2U:

"No this is not exactly what I meant. In your example I would like to get the result 2
for entering the date 2/01/2001. "

...but you said you wanted a three column, 100 row offset? Anyway, if all you want to do is a "left lookup", simply remove the +10 from the formula I posted:

=INDEX(A2:D21,MATCH(F1,D2:D21,0),1)

Paddyd · Apr 6th 2003

one possible solution:

assuming that the only thing that can turn up in the "date" area is a X, insert another column that checks for whether a cross has been entered. something like:

=IF(SUM(LEN(D7:AH7)),"X","")

...array entered using control + shift + enter, not just enter, could be used to perform the check. you can then use a simple sumif() to get the result:

=SUMIF(AJ7:AJ16,"X",C7:C16)

obviously, you could hide the column that contatined the first calculation (AJ in this example)

paddy

Paddyd · Apr 4th 2003

your attachement's knackered!

that said:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=7><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book6</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows Windows 2000 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=7><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb279821><INPUT onclick='window.clipboardData.setData("Text",document.formFb604324.sltNb834045.value);' type=button value="Copy Formula" name=btCb815249></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=7><TABLE border=0><TBODY><TR><FORM name=formFb604324><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb604324.txbFb640222.value = document.formFb604324.sltNb834045.value" name=sltNb834045><OPTION value==INDEX(A2:D21,MATCH(F1,D2:D21,0)+10,1) selected>F2</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value==INDEX(A2:D21,MATCH(F1,D2:D21,0)+10,1) name=txbFb640222></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">3 columns back</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Lookup</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Input:</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">2/01/2001</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">1/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">Result:</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"><A onclick="document.formFb604324.sltNb834045.options[0].selected=true; document.formFb604324.txbFb640222.value = document.formFb604324.sltNb834045.value;" href="#javascript:void(0);">12</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">2</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">2/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">3</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">3/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">4</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">4/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">5</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">5/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">6</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">6/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">7</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">7/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">8</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">8/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>10</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">9</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">9/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>11</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">10</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">10/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>12</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">11</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">11/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>13</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">12</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">12/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>14</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">13</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">13/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>15</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">14</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">14/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>16</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">15</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">15/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>17</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">16</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">16/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>18</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">17</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">17/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>19</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">18</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">18/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>20</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">19</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">19/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>21</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">20</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid">20/01/2001</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=7><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

the formula is:

=INDEX(A2:D21,MATCH(F1,D2:D21,0)+10,1)

change the references to suit, and the +10 to +100 to get the appropriate offset.

...is that what you meant?

paddy

Paddyd · Apr 4th 2003

with the data as in you attached workbook:

=MAX(IF((A2:A2413>=0.3)*(A2:A2413<=1),B2:B2413))

...returns 279.04. The formula needs to be array entered using control + shift + enter, not just enter. You could also consider using dmax() - check it out in the help file.

paddy

Paddyd · Apr 3rd 2003

via U2U:

"=SUMIF(OFFSET($W$4,ActualDays,0):$W$370,R57,OFFSET($aa$4,ActualDays,0):$aa$370)
I'm trying to summarise a forecast which has data for all the remaining days in this year by day of the week (DOW). R57 is the referance value for the day to sumarise for. Column W has the DOW value for each date. Column AA has the forecast values."

Posts by Paddyd

SUMIF Array Formula.... [SOLVED]

Problem writing formula

filtering [SOLVED]

checking to see if certain cells sum to 24 hours

Graphing

Graphing

Pivot tables do not refresh contents when data is deleted an

Blank cell issues

Blank cell issues

Converting Text To Assigned Value?

Converting Text To Assigned Value?

Converting Text To Assigned Value?

Pivot Tables.

[Solved] Selecting variable range for formulas

subtracting 2 values [SOLVED]

offset function

Sum when numbers are indicated by "X" [SOLVED]

offset function

picking points [SOLVED]

Eratic formula results