If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
set formula to display a data from a 5 columns table
I have a table from A1 - E5:
0-10 day 11-20 day 21-30 day 31-40 day $1 - $1,000 15 20 25 30 $1,001-$2,000 35 40 45 50 $2,001-$3,000 55 60 65 70 How can I setup a formula in A10 to display the number from the table if I key in the $ amount in A8 and the day in A9? Example: input A8=$900, input A9= 12 day, output in A10 should be "20" |
#2
|
|||
|
|||
set formula to display a data from a 5 columns table
=OFFSET(A1,ROUNDDOWN((A9/10)-0.1,0),(ROUNDUP(A8/1000,0)+1))
-- Gary''s Student - gsnu200793 |
#3
|
|||
|
|||
set formula to display a data from a 5 columns table
"Gary''s Student" wrote: =OFFSET(A1,ROUNDDOWN((A9/10)-0.1,0),(ROUNDUP(A8/1000,0)+1)) -- Gary''s Student - gsnu200793 Thanks you for your answer. I tried but got some wrong return. Did I enter something wrong? I enter 900 in A8 and 10 in A9. The display in A10 is "11-20 day" |
#4
|
|||
|
|||
set formula to display a data from a 5 columns table
Hi
Try amending Gary's formula to =OFFSET(A1,(ROUNDUP(A8/1000,0)),ROUNDDOWN((A9/10)-0.1,0)+1) -- Regards Roger Govier "David" wrote in message ... "Gary''s Student" wrote: =OFFSET(A1,ROUNDDOWN((A9/10)-0.1,0),(ROUNDUP(A8/1000,0)+1)) -- Gary''s Student - gsnu200793 Thanks you for your answer. I tried but got some wrong return. Did I enter something wrong? I enter 900 in A8 and 10 in A9. The display in A10 is "11-20 day" |
Thread Tools | |
Display Modes | |
|
|