Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display data values in columns on bar chart Stu[_2_] Excel Discussion (Misc queries) 1 May 15th 08 11:48 AM
Compare several columns of data and display unique ones [email protected] Excel Discussion (Misc queries) 0 March 13th 07 04:12 PM
Display columns side by Side in the Data Area of a Pivot table Sue Excel Discussion (Misc queries) 1 March 21st 06 02:12 AM
Can I suppress display of duplicate data in columns? Bob Cassano Excel Discussion (Misc queries) 1 June 14th 05 02:57 PM
Need Formula to display pivot table source data Don S Excel Worksheet Functions 3 February 23rd 05 10:13 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017