Remember Me?

#1
June 21st 08, 09:01 PM posted to microsoft.public.excel.setup
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,560
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
June 22nd 08, 11:46 AM posted to microsoft.public.excel.setup
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 11,058
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
June 22nd 08, 07:16 PM posted to microsoft.public.excel.setup
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,560
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
June 25th 08, 01:23 AM posted to microsoft.public.excel.setup
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,480
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"

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Stu[_2_] Excel Discussion (Misc queries) 1 May 15th 08 11:48 AM [email protected] Excel Discussion (Misc queries) 0 March 13th 07 04:12 PM Sue Excel Discussion (Misc queries) 1 March 21st 06 02:12 AM Bob Cassano Excel Discussion (Misc queries) 1 June 14th 05 02:57 PM Don S Excel Worksheet Functions 3 February 23rd 05 10:13 PM

All times are GMT +1. The time now is 01:53 AM.