Import data from a table
 

I tried to retreive the data from the table but I got an error. Please let me
know the mistake in my formular. The table is (B2:F5):
0-30 days 31-37 38-44 45-51 days
$0-$10,000 225 345 465 585
$10,001-$15,000 395 505 625 745
$15,001-$20,000 565 685 805 925

I keyed in 10,000 in B8 and 52 in B9 and the output is in B10:

10000 (B8)
52 (B9)
$0-$10,000 (B9 output)

My formular is:
=OFFSET(B2,ABS(ROUNDUP(B8/5000-1,0)),IF(B9<31,1,IF(B9<38,2,IF(B9<45,3,IF(B9<52,4) ))))
I checked the function arguments and was told it has the volatile. Please
re-set a new formular for me if mine is not working. Thanks,

Import data from a table
 

Your value in B9 is greater 51. Your offset formula's last check is
IF(B9<52,4). Do you have need of a number if it falls outside the dateline?
Perhaps you should have a column G with data, and the header in G2 would be
52+ days. Then you could modify the portion of your offset statement like
this, IF(B9<52,4,5). Or something to that effect. If the value in the 45-51
days is also the data that would be for 52+days, you can delete the nested
B9<52 portion, and modify like this IF(B9<45,3,4).
--
John C


"David" wrote:

I tried to retreive the data from the table but I got an error. Please let me
know the mistake in my formular. The table is (B2:F5):
0-30 days 31-37 38-44 45-51 days
$0-$10,000 225 345 465 585
$10,001-$15,000 395 505 625 745
$15,001-$20,000 565 685 805 925

I keyed in 10,000 in B8 and 52 in B9 and the output is in B10:

10000 (B8)
52 (B9)
$0-$10,000 (B9 output)

My formular is:
=OFFSET(B2,ABS(ROUNDUP(B8/5000-1,0)),IF(B9<31,1,IF(B9<38,2,IF(B9<45,3,IF(B9<52,4) ))))
I checked the function arguments and was told it has the volatile. Please
re-set a new formular for me if mine is not working. Thanks,