formula finding price based on volume bracket

I want to write a formula, say in cell E2 that picks the 
correct price based on valume Entered in Cell D1.

Suppose I have a table set up with volume vs price. sush 
as:

Column       A        B         C          D            E
Row   1    From      TO       PRICE   Volume Entered    ??
Row   2     500      600      $1.20                   
Row   3     601      700      $0.99      

Etc... down the row.  I know I can use vlookup ordinarily 
but now there are two columns.  If volumn is between 500 
to 600 then the price is one thing, if volumn is between a 
different braket is something else.........
What is the best formula to use for this?

I used one of the suggestions:  =vlookup(D1,A2:C100,3,1), 
however, it gives me wrong answers.  
Any other help will be appreciated. Thanks



0
anonymous (74722)
7/23/2004 12:16:46 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
838 Views

Similar Articles

[PageSpeed] 40

I dont know exactly how your sheet is laid out but I think this ma
work

=vlookup(D1,c2:e100,3,1)

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 12:57:00 AM
A restructuring of your price table
would enable use of VLOOKUP ..

In Sheet1
-------------
Assume you have the price table below in A1:B5
(where "Vol" in col A is in ascending order)

Vol UnitPrice
1 $1.50
500 $1.20
600 $0.99
700 $0.80

The above pricing structure means:

Vols <500, $1.50
Vols >=500 and <600, $1.20
Vols >=600 and <700, $0.99
Vols >=700 and above, $0.80

In Sheet2
-------------
Assuming the order vols
will be listed in col A, row2 down

To extract the correct unit prices for the vols in col A:

Put in B2: =VLOOKUP(A2,Sheet1!$A$2:$B$5,2,TRUE)
Copy down

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"thrava" <anonymous@discussions.microsoft.com> wrote in message
news:25f901c4704a$572415d0$a401280a@phx.gbl...
> I want to write a formula, say in cell E2 that picks the
> correct price based on valume Entered in Cell D1.
>
> Suppose I have a table set up with volume vs price. sush
> as:
>
> Column       A        B         C          D            E
> Row   1    From      TO       PRICE   Volume Entered    ??
> Row   2     500      600      $1.20
> Row   3     601      700      $0.99
>
> Etc... down the row.  I know I can use vlookup ordinarily
> but now there are two columns.  If volumn is between 500
> to 600 then the price is one thing, if volumn is between a
> different braket is something else.........
> What is the best formula to use for this?
>
> I used one of the suggestions:  =vlookup(D1,A2:C100,3,1),
> however, it gives me wrong answers.
> Any other help will be appreciated. Thanks
>
>
>


0
demechanik (4694)
7/23/2004 1:17:51 AM
> Vols >=700 and above, $0.80
sounds a little redundant ..

should read better as:
> Vols >=700, $0.80

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
7/23/2004 1:46:29 AM
thank you much Max.

I'll implement it and if there are problems, I'll repost.  
thanks again



>-----Original Message-----
>> Vols >=700 and above, $0.80
>sounds a little redundant ..
>
>should read better as:
>> Vols >=700, $0.80
>
>--
>Rgds
>Max
>xl 97
>---
>Please respond in thread
>xdemechanik <at>yahoo<dot>com
>----
>
>
>.
>
0
anonymous (74722)
7/23/2004 6:28:17 PM
Pleasure' Thrava !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"thrava" <anonymous@discussions.microsoft.com> wrote in message
news:2e9d01c470e2$d2b5b4e0$a601280a@phx.gbl...
> thank you much Max.
>
> I'll implement it and if there are problems, I'll repost.
> thanks again


0
demechanik (4694)
7/23/2004 7:38:14 PM
I've tested this Max,and your restructured table works 
like a charm.

Thank you so much.
Thrava

>-----Original Message-----
>A restructuring of your price table
>would enable use of VLOOKUP ..
>
>In Sheet1
>-------------
>Assume you have the price table below in A1:B5
>(where "Vol" in col A is in ascending order)
>
>Vol UnitPrice
>1 $1.50
>500 $1.20
>600 $0.99
>700 $0.80
>
>The above pricing structure means:
>
>Vols <500, $1.50
>Vols >=500 and <600, $1.20
>Vols >=600 and <700, $0.99
>Vols >=700 and above, $0.80
>
>In Sheet2
>-------------
>Assuming the order vols
>will be listed in col A, row2 down
>
>To extract the correct unit prices for the vols in col A:
>
>Put in B2: =VLOOKUP(A2,Sheet1!$A$2:$B$5,2,TRUE)
>Copy down
>
>--
>Rgds
>Max
>xl 97
>---
>Please respond in thread
>xdemechanik <at>yahoo<dot>com
>----
>"thrava" <anonymous@discussions.microsoft.com> wrote in 
message
>news:25f901c4704a$572415d0$a401280a@phx.gbl...
>> I want to write a formula, say in cell E2 that picks the
>> correct price based on valume Entered in Cell D1.
>>
>> Suppose I have a table set up with volume vs price. sush
>> as:
>>
>> Column       A        B         C          D            
E
>> Row   1    From      TO       PRICE   Volume 
Entered    ??
>> Row   2     500      600      $1.20
>> Row   3     601      700      $0.99
>>
>> Etc... down the row.  I know I can use vlookup 
ordinarily
>> but now there are two columns.  If volumn is between 500
>> to 600 then the price is one thing, if volumn is 
between a
>> different braket is something else.........
>> What is the best formula to use for this?
>>
>> I used one of the suggestions:  =vlookup
(D1,A2:C100,3,1),
>> however, it gives me wrong answers.
>> Any other help will be appreciated. Thanks
>>
>>
>>
>
>
>.
>
0
anonymous (74722)
7/23/2004 8:45:02 PM
Glad to hear that, Thrava!
The feedback is appreciated ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Thrava" <anonymous@discussions.microsoft.com> wrote in message
news:319601c470f5$ed751bf0$a301280a@phx.gbl...
> I've tested this Max,and your restructured table works
> like a charm.
>
> Thank you so much.
> Thrava


0
demechanik (4694)
7/23/2004 10:53:46 PM
Reply:

Similar Artilces:

Help: MAPI can't find PSTPRX.DLL
Hi there Can anyone suggest how I can make outlook 2002 work properly. Everytime I press send/receive it says MAPI can not locate PSTPRX.DLL. I have done search and it is not on my computer. Please can anyone assist? just trying out 1st time >-----Original Message----- >Hi there > >Can anyone suggest how I can make outlook 2002 work >properly. Everytime I press send/receive it says MAPI can >not locate PSTPRX.DLL. I have done search and it is not >on my computer. Please can anyone assist? >. > See if this info helps: http://support.microsoft.com/default.asp...

Formula Too Long
Is there anyway to expand the amount of characters that a formula will allow? I am trying to link 14 different spreadsheets to one and I keep getting the formula is too long error. Any help will be appreciated. Thanks in advance. Todd try creating a name for the link then =Joe*bill "Todd" <todd@yahoo.com> wrote in message news:0e3901c38f2f$0f8a1b90$a101280a@phx.gbl... > Is there anyway to expand the amount of characters that a > formula will allow? I am trying to link 14 different > spreadsheets to one and I keep getting the formula is too > long error. ...

Excel formulas #23
NEED HELP...I have a formula set up to where it pulls data from a linked sheet, I want to be able to click on the right bottom corner of where I have the formula and drag it to populate accordingly to other rows...this works but instead of pulling the information vertically which is what I need it is pulling the info horizontaly from my linked sheet...how can I change the orientation of where it pulls the info...i am dragging horizontal but want the info from the other sheet to propogate using the vertical info order....hope I made sense...anyone that can help me?...I would really appr...

Quick Excel formula question before I go on holiday tomorrow!
I would like to keep track of my credit card expenditure while away on holiday Florida. I was thinking of an Excel spreadsheet I could keep on my PDA - something along the lines of; A B C 1 AVAILABLE 2000 UKP 3600 USD 2 3 PARKING 70 ??? 4 HOTEL ??? 250 5-20 etc. etc. 21 SPENT 208 376 22 BALANCE 1792 3224 I have taken the conversion rate in the example above from...

Returning the x value of a formula
The value of "cell A1" is "cell A2" percent of X. How do I write a formula in cell A3 that will always return X. =a1/a2% HTH, Bernd ...

Calculate Gestational Age based on months and weeks
Hi, SO I want to make a pregnancy wheel: (LMP(Date) - 3months, +7d)= Estimated Date of Delivery (it also has to advance to the next year if >April. Also, I want to be able to automaticallt update the current Gestational age(#weeks/days since LMP to current date) based on today's date and the LMP. I found this code online at a website that calculates Gestational age in online calculator. Can it be converted for use in access? Thanks! <script> // current equation code function PregDates() { var lmpid = $("#lmp").val(); var ddid = $("#duedate...

How do you find and edit the dictionary in Publisher?
I want to delete words in the Publsiher dictionary. Can't find it! Can edit dictionary in Word. Isn't the Word dictionary the one used in Publisher? -- Don Vancouver, USA "chuckthe3" <chuckthe3@discussions.microsoft.com> wrote in message news:9D363986-DEE1-46B2-8443-12CD6F7EE672@microsoft.com... > I want to delete words in the Publsiher dictionary. Can't find it! Can edit > dictionary in Word. You can change your custom dictionary in your newsreader (Outlook Express), tools, options, spelling tab, edit custom dictionary. -- Mary Sauer MS MVP h...

macro which finds last cell in a column
please help me by telling a macro which finds last cell in a column thank -- Message posted from http://www.ExcelForum.com Dim LastRow as Long LastRow = Range("A65536").End(xlUp).Row or if you just want to select it: Range("A65536").End(xlUp).Select Regards Trevor "vikram >" <<vikram.15hp0w@excelforum-nospam.com> wrote in message news:vikram.15hp0w@excelforum-nospam.com... > please help me by telling a macro which finds last cell in a column > > thanks > > > --- > Message posted from http://www.ExcelForum.com/ > ...

How to find free News-Server about science?
Hi, i hope someone can help me to find free Servers with newsgroups about sciences , medicine, pharmacie and so one. I'm total new and I use Outlook to show all informations. Thanks Kerstin Ask your ISP or Google it. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Kerstin Hummel asked: | Hi, | | i hope someone can help me to find free Servers with newsgroups about | sciences , medicine...

Addition to a working formula
I have this formula and it works fine. =IF(OR(S140="No",S140="0"),"",G140*X140) This is placed in cell p159 I now need to add the exact same information as above to the cells immediately below the cell listed above. so Orinal cells involved G140 = data s140 = yes/no x140= % I now need the answer of g141 s141 and x141 to be added to the reult of g140 s140 x141 in p159 if 'yes' is placed in s141, but not added if 'no' is entered into s141 To summarise - i need the answers of the 140 and 141 lines to be placed ...

excel formula 01-26-10
Hi Friends Im facing a problem with the small issue will u please clarify me the formula A1 OK A2 OK A3 OK A4 NO A5 OK A6 OK A7 OK A8 NO A9 ? here the problem with the A9, I need the value of the A9 should be Perfect when A1 to A8 are OK if we have any one of this A1 to A8 as NO the value should be return as Pending If u can help me i will be thankful for U friend One way In A9: =IF(COUNTIF(A1:A8,"OK")=8,"Perfect","Pending") Yes?, hit the YES below -- Max Singapore --- "sudheer" wrote: > Im fac...

Automate creation of excel files based on the field value
How can I automate creating excel files based on the different values in a field ? I have a table where a field has about 100 dfifferent values. I want to create an excel file containing all the records where this field =x, then =y, etc without having to run a query 100 times. Thanks ...

Formula needed #5
I need a formula to figure out how many months an employee has worked since the date of hire. Thank you in advance for your help. "accessnovice" <accessnovice@discussions.microsoft.com> wrote in message news:24A9BFC5-9EDE-4B00-9688-4A829B2D4882@microsoft.com... >I need a formula to figure out how many months an employee has worked since > the date of hire. Thank you in advance for your help. > =DATEDIF(K1,TODAY(),"m") where A1 holds the hire date. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mail...

Subtraction formula with Text error
This is a timesheet: A1 B1 C1 D1 E1 F1 Time 9:00 am 12:00 pm 9:00 am 12:00 pm OFF Total 3 3 #Value! I have time in and time out with total hours caculating. The formula I am using for total hours is a simple C1-B1 with HH:MM format. I need to enter OFF, SICK, OUT for days that the employee is not in but I get an error value. How can I make it so that the formula adds the hours entered and ignores any other values such as OFF, SICK, OUT. >The formula I am us...

I find a job in the position of hotel above three-star
I want a job in hotel above three-star with some experience,help me!!! On Thu, 20 Mar 2008 17:52:24 +0800, "Summery" <thomasimao@126.com> wrote: >I want a job in hotel above three-star with some experience,help me!!! > Well, posting this request, ungrammatically and badly punctuated, in a technical support newsgroup for Microsoft Access databases is not a very hopeful start. Check with an employment agency in your area, or visit some hotels. -- John W. Vinson [MVP] ...

Conditional formula
I have a spreadsheet where I am trying to do two things: First: I want to create a forumula in column "Z" that looks at the number in column "Y" and if it's a negative, then multiply the number in column "X" by 1.1. If the number in column "Y" is a positive number, then just insert the number in column "X". So: Column X number is $100 Column Y number is -20% Column Z number would be $110 Column X number is $100 Column Y number is 20% Column Z number would be $100 Second: In column AA, I want to perform a ...

Need HELP with Formula #12
I have two spread sheets in one work book. The first sheet is named DRA SCHEDULE 1-9 and the second is named DRAW SCHEDULE 10-18. On the firs sheet in Y21 there is this formula that reads like this =sum(d21:u21) I need to know what the formula is to add y21 in the first sheet t d21:u21 in the second sheet. The annswer will appear in y21 on th second sheet. Your feedback is most appreciated. Thanks for you help : -- k.floy ----------------------------------------------------------------------- k.floyd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2867 View th...

Help me please, with a formula!
Hi everyone, i need help with the following please: Cell B16 contains a date (which is validated so it is a wednesday) If no data inputed in to cell B27 (9 days > then the date in cell B16) i want cell B27 to turn red. However if cell b16 contains no data then I want cell B27 to do nothing. Hope that makes sense. Many many thanks Dan ...

Advanced Find
Does any one know how to get the Address 2 Freight Terms and Shipping Methods picklists to appear in the advanced find field list ? I can get custom fields to appear, but the above fields were used to create 'custom' picklists when V1.2 was being used and now that we have upgraded we cannot search on them. Anne B ...

Turning value of formula into actual cell content?
Hi. I've a sheet with a few thousand rows where in column B I need to add a prefix to every value. What I did was in column M fill the cells with the prefix, and made column N a copy of column B. Then in Column B I made the function: =M2&""&N2 and copied it all the way down (with incrementing numerals). That works great. But, I need to be able to copy-n-paste the new prefixed value of some of the cells in column B into another spreadsheet. But when I try, what I'm copying is the formula, not the resulting value. Is there a way to copy the values generated by the formu...

Skip pattern based on text box value
Hi, I have a text control. People will be typing either words or numbers into it. If they type 99 I want to skip down several questions. Here's the condition for my macro: [Forms]![frmFB213]![FB215]=99 The action is 'Go to control' and the control name points to question FB229. I've put my macro in the AfterUpdate section, but nothing is happening. I'm suspecting this is because the answer is free-text. All my skip patterns based on combo boxes are working well. I've tried putting single and double quotes around the 99, I've tried using the "Like&q...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

query on records based on time interval
If I have a table something like this:startdate | stime | endate | etime | tool3/3/07 | 8:47 | 3/4/07 | 00:30 | A3/4/07 | 3:57 | 3/4/07 |4:30 | B3/4/07 |8:21 | 3/4/07 |9:20 | CCurrently I have a form which use to open dailyrecord.I have one textbox([date]) for enter date and a button to view.Is it possible when I wan to open a new form with all the records on3/4/07it will only give me the records from 3/3/07 7:00 till 3/4/07 7:00(thus, only show tool A and tool B)my currently event procedure for the button is below:Dim strSQL As StringDim strWhere As StringIf Not I...

Formula #19
Hi, I have two speadsheets. One with my bank statement and on with my girlfriends. I am trying to make it to where my balance shows on hers and her balance shows up on mine. Is there anyway to do this. Emory Get married then YOU won't have a balance to worry about<g> Open both workbooks then Window>New Window>Arrange>Vertical. You should have both workbooks side by side in the window. In a cell on your workbook(sheet) where you want her balance placed enter an equal(=) sign. Select her workbook(sheet) balance cell and hit <ENTER>. Do same other way around. C...

Excel formula not working
I encounter a Excel problem, let me explain it in a examble: A1=10 B1=20 C1=2000 D1=20 E1=C1-A1-B1-C1 E1 will shows value 1950 Then Copy E1 all the column down to E10 There are not values in cells below A1, B1, C1, and D1. Cells E2~E10 should show value 0 However, they show 1950, the same value as E1. ANyone have this problem before? Hi (ignoring the error in the formula in E1) check that tools / options / calculation is set to automatic if that is not the problem please copy & paste the formula in cell E2 into your reply psot. Cheers JulieD "KC Mao" <kc@sinzon.com...