Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Range Import to a Cell not Table | Excel Discussion (Misc queries) | |||
Pivot Table Data Import | Excel Discussion (Misc queries) | |||
Import External Data Non-HTML Table | Excel Discussion (Misc queries) | |||
Is it possible to import external data into existing pivote table | Excel Discussion (Misc queries) | |||
external import of multiple worksheet data & create pivot table | Excel Worksheet Functions |