advanced comparison between columns

N

NickV

Hi,

I was wondering if somebody knows the answer to my problem, which i
the following:

I have a dataset of 100 columns, each representing a year. Every ro
represents a house, that has been sold several times during those 10
years. For example, if a house is sold in 1843 for $3000, next in 187
for $4000 and in 1900 for $10000, I would like to compute the return
for each of those transactions.
The problem is that there are varying numbers of columns between th
columns in which the transactions are recorded, that contain no values

Does anybody know how to compute the differences between these value
within a row?

Thanks,

regards,

Nic
 
F

Frank Kabel

Hi
could you post some example rows (plain text - no attachment please).
Do you really have blank rows in between?
 
N

NickV

An example row is:


Code
-------------------

1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898
17000 11750

-------------------


So I need to calculate the difference for these 2 years, but every ro
has blanks at diff locations. So I need to search for a value that i
not zero and for the second value that is not zero and then subtrac
them.

After that i need to find the 3rd value that is not zero and subtrac
that from the second one. etc
 
F

Frank Kabel

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(1:1,1,SMALL(IF(1:1<>"",COLUMN(1:1)),2))-INDEX(1:1,1,MIN(IF(1:1<>
"",COLUMN(1:1))))

and for 3-2:
=INDEX(1:1,1,SMALL(IF(1:1<>"",COLUMN(1:1)),3))-INDEX(1:1,1,SMALL(IF(1:1
<>"",COLUMN(1:1)),2))
assumption: your data is in row 2

--
Regards
Frank Kabel
Frankfurt, Germany

NickV > said:
An example row is:


Code:
Click to expand...
1894 1895 1896 1897 1898
 
N

NickV

w0w, thx man... However didn't completely work for me. Maybe it i
because of the row/collumn numbers in the formula, tried to change bu
didn't succeed. This is more complete table:

Code
-------------------

Straatnaam House Nr. Sectie 1839 1840 1841 1842 1843 1844 1845 1846
Amstel 37 P276 3500 3850


-------------------

Years starts in collumn 4
Houses start in row 4

Hope you can help me once more, thanks!!
 
F

Frank Kabel

Hi
try
=INDEX(D2:IV2,SMALL(IF(D2:IV2<>"",COLUMN(D2:IV2)),2))-INDEX(D2:IV2,1,MI
N(IF(D2:IV2<>
"",COLUMN(D2:IV2))))
 
N

NickV

Thanks again appreciated, however i still encounter problems. I assum
with your formule:

Code
 
F

Frank Kabel

Hi
if nyour data is in column 4 this is correct. For the circular
reference: In which cell did you playce this formula. It has to be in a
different row

--
Regards
Frank Kabel
Frankfurt, Germany

NickV > said:
Thanks again appreciated, however i still encounter problems. I assume
with your formule:

Code:
Click to expand...
 
N

NickV

Very stupid yes, in a different row it worked. However I now get th
value 0 while it should be giving:

3850 - 3500 = 35
 
F

Frank Kabel

Hi
sorry, small mistake. Try
=INDEX(A4:IV4,SMALL(IF(D4:IV4<>"",COLUMN(D4:IV4)),4))-INDEX(A4:IV4,1,MI
N(IF(D4:IV4<>"",COLUMN(D4:IV4))))
 
N

NickV

wow i actually got a number right now, however not the correct one :(
The first value in the row is 6500
Then some blanks then 8250
Then value 17000
Then some blanks and fourth value is 11750

When i entered the formula the output was 5250. So he substracted th
fourth from the first value. Whilst it should be doing at first th
second one from the first one. So the output should be 8250 - 6500
175
 
F

Frank Kabel

Hi
if you like email me your file and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de
 
Top