Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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
Posted to microsoft.public.excel.setup
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display data values in columns on bar chart | Excel Discussion (Misc queries) | |||
Compare several columns of data and display unique ones | Excel Discussion (Misc queries) | |||
Display columns side by Side in the Data Area of a Pivot table | Excel Discussion (Misc queries) | |||
Can I suppress display of duplicate data in columns? | Excel Discussion (Misc queries) | |||
Need Formula to display pivot table source data | Excel Worksheet Functions |