Extraction and printing of values from every 12th cell of row
Posted by John Davidson on January 17, 2001 2:59 PM
Here is my problem.
I am creating a financial analysis spreadsheet that will extract specific data from an amortization schedule into a table that I have created. This amortization data is contained in an amortization schedule that is on the same spreadsheet page as the table. The problem starts with a logical test to determine if it is correct to extract the first number from a column in the amortization schedule. If this logical test determines a "yes" result, then this first number will be extracted and printed in that "test" cell. Here now lies my problem. After printing the first number, I now need to extract a series of numbers from the same column in the amortization schedule and print them in a row of cells in the spreadsheet table. The numbers that need to be extracted from the amortization schedule column are located every 12th cell in that column.
Any ideas. I am stuck!!
Posted by Aladin Akyurek on January 18, 2001 12:48 AM
I'm not sure I interpret what you want correctly.
I'll assume that
(a) the numbers you are interested in is in column A with the first number in A1.
(b) your test cell is B1.
(c) you want to extract/copy/read off the contents every 12th cell from A into column C, starting in C1, iff there is a number in B1.
Enter the following formula in C1 and copy down as far as needed.
=IF(ISNUMBER($B$1),OFFSET($A$1,ROW()*12-1,0),"")
If the number in the test cell is always greater than zero when the test positive, you may replace ISNUMBER($B$1) by just $B$1. In order to understand the second arg of OFFSET, you have to know the syntax of the OFFSET-function (you can find the necessary info via Contents and Index under Help of Excel).
You say "print them in a row of cells in the spreadsheet table". I interpreted this as "in the cells of a column". If you want these numbers in the cells of a given row instead, you need to adjust the formula above. Supposing that these numbers must occupy the cells
D1,E1,F1, and so on, then enter in D1
=IF(ISNUMBER($B$1),OFFSET($A$1,(COLUMN()-3)*12-1,0),"")
and copy it to the cells next to D1.
Hope all this is what you wanted.
Aladin
