If-Then Statement #4

Column A = Part Used    (not needed for this part)
Column B = Weight of Item
Column C = Company Used  (I found this using If-Then, an easy one..)
Column D = Price Paid

Column D is what I am trying to figure out.... what price they pai
according to the weight of the item.
So if the weight was 5 pounds, they used Quick Freight and paid $3.
If the weight was 10-50 pounds, they used Quick Freight and paid $20.
If the weight was 51-70 pounds, they used Gamecock Freight and pai
$40.
If the weight was 71-90 pounds, they used Gamecock Freight and pai
$46.


So for Column D, all the answers will either be $3,$20,$40, or $46 an
thats it.
I have 6,868 parts and weights, so I cant just do it manually.

I tried the Vlookup thing from above, and it said N/A..

Thanks for help.   :

--
alan
-----------------------------------------------------------------------
alanf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1536
View this thread: http://www.excelforum.com/showthread.php?threadid=26983

0
10/16/2004 4:29:20 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
354 Views

Similar Articles

[PageSpeed] 7

alanf Wrote:
> Column A = Part Used    (not needed for this part)
> Column B = Weight of Item
> Column C = Company Used  (I found this using If-Then, an easy one..)
> Column D = Price Paid
> 
> Column D is what I am trying to figure out.... what price they pai
> according to the weight of the item.
> So if the weight was 5 pounds, they used Quick Freight and paid $3.
> If the weight was 10-50 pounds, they used Quick Freight and paid $20.
> If the weight was 51-70 pounds, they used Gamecock Freight and pai
> $40.
> If the weight was 71-90 pounds, they used Gamecock Freight and pai
> $46.
> 
> 
> So for Column D, all the answers will either be $3,$20,$40, or $46 an
> thats it.
> I have 6,868 parts and weights, so I cant just do it manually.
> 
> I tried the Vlookup thing from above, and it said N/A..
> 
> Thanks for help.   :)

With this layout, change my formula to...

=VLOOKUP(B2,CHOOSE(MATCH(C2,{"Quick Freight","Gamecoc
Freight"},0),QuickFreight,GamecockFreight),2)

Also, don't forget to set up your tables and name them using Insert 
Name > Define.

You may want to change the tables to the following...

Quick Freight...

1.....$3.00 
10.....$20.00 
51.....#N/A

Gamecock Freight...

51.....$40.00 
70.....$46.00 
91.....#N/A

Does this help

--
Domeni
-----------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=26983

0
10/16/2004 4:55:17 PM
Reply:

Similar Artilces:

after 4.0 upgrade emails are no longer being associated with cases
We have a workflow rule that automatically fires whenever a new case is created to send an email with the case number, owner, etc. to the customer. When we were running MSCRM 3.0, the automated email would be automatically associated with the case. That is, the Emails regarding field would automatically be filled in with the associated case. After a recent upgrade to 4.0, the workflow rules still fire and emails still get automatically created when a case is created, but the regarding field is blank. Our support reps now have to go into the Contact record, find the automated email, a...

Excel Spreadsheet #4
How do i change a spreadsheet to an A3 size? It's in the printer settings -- Regards, Peo Sjoblom "teletubby" <teletubby@discussions.microsoft.com> wrote in message news:0366922A-B1C3-48B4-843F-EF649F76E7F0@microsoft.com... > How do i change a spreadsheet to an A3 size? ...

Excel #4
I wish to type a series of numbers in a column, such as invoice numbers example: 3-1900, 3-1901 etc. However, whenver I do this, I get the date Mar-00. Any advice? Format the cells as text before entering the invoice number. Janet wrote: > > I wish to type a series of numbers in a column, such as > invoice numbers example: 3-1900, 3-1901 etc. However, > whenver I do this, I get the date Mar-00. Any advice? You can try either formatting the cells as text before you enter your data, or preceed your data with an apostrophe (as in '3-1900). Ed "Janet" <e...

duplicate messages #4
when a user emails a distribution list that the user is a member of, he/she receives the email. is there a way to prevent sending the user the email when he/she sends it to a distribution list that he/she is a member of? Hi I would request you to use RULE option under TOOLS tab menu in Microsoft Outlook. For more information you can get in touch with me at arorarahul2000@hotmail.com "noone" wrote: > when a user emails a distribution list that the user is a member of, he/she > receives the email. > > is there a way to prevent sending the user the email when...

Duplicate detection #4
Hi, I've created a duplicate detection rule with to criteria : Exact last name And first 20 Character of Company name I thought it would be a AND, so the rule will detect only the Lead that has both criteria but it seems to be a OR. How can I change it to make what I want to do? Thanks, Hi, the only way to do this is create another duplication rule for the second condition... Everything in one rule == AND different rules == OR "Val" wrote: > Hi, > > I've created a duplicate detection rule with to criteria : > Exact last name > And first 20 Character ...

A/R Statement info
HI all, I got a question on the Statement details, most likely the statement details come from the table RM20101 and some of the applied payment come from the table RM 20201, sometimes the payment will appear twice in both table, under what circumstances will this happen? Also, I find that some of the entries in RM20201 will not appear in the statement even the APFRDCDT (apply date) is less than the cut-off date. Why will this happen? Thanks a lot! Regards, Ricky ...

Matching bill pay items to statement items
Hello All... I'm using Money 2004 Deluxe. I go onto my banks bill pay and pay a bill. The bill pay program creates a .ofx file which Money imports. The entry is flagged with an E in Money. Problem is when I download a statement from my bank which has the bill pay item in the statement Money does not match the bill pay item to the bill pay item in the statement. When I try to manually match the two within Money, the original bill pay item is greyed out. I have Money setup to match transactions within 30 days, but both the bill pay entry and the statement entry are the same date. An...

Hyperlinks in Excel #4
Using Excel 2000 is there a way to remove hyperlinks such that the background formatting is not also removed? I have a user who needs to remove hyperlinks from e-mail addresses, in a list, and when she does, all the formatting (font, borders, shading, etc.) is also removed. - Thanks. -- LPS I'm assuming that the user is running a macro that looks essentially like this: Selection.hyperlinks.delete If you really wanted, you could find out all the formatting for each of those cells, remove the hyperlink and then reapply the formatting. Myself? I'd just reapply the formatting tha...

AfxBeginThread failure
In an MFC pgm under VS2005sp1, I am creating a worker thread using AfxBeginThread. Under certain circumstances which I can't quite pin down, AfxBeginThread returns NULL, indicating it has failed. Is there any way I can find out the cause of the failure, such as an error code? I have tried tracing through the code with the debugger, but what is apparently detecting the error condition is in MS code for which I don't have the source. My specific code is CWinThread* pThread=AfxBeginThread(MyControlFunction,&ti,0/*priority*/, 230000000/*stack size*/); and a test for pThread==NULL im...

Outlook shuts down #4
Hi I have Outlook 2003 under XP with Hauri anti virus all updated. I have several spam msgs in the inbox and if I highlight them to delet or move the pgm shuts off immediately every time. Any clues? Thanks ...Lyl ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

CRM 4.0 MSDN
I noticed that 4.0 has been posted on MSDN but has anyone found where to access the install codes? On Jan 15, 5:39=A0pm, dmn8 <d...@discussions.microsoft.com> wrote: > I noticed that 4.0 has been posted on MSDN but has anyone found where to > access the install codes? You can download CRM 4.0 here : http://www.microsoft.com/downloads/results.aspx?DisplayLang=3Den&nr=3D20&fre= etext=3DCRM&sortCriteria=3Ddate If you mean the license keys, then they haven't been released yet :( -- Robert MacLean http://www.sadev.co.za "dmn8" wrote: > I noticed that...

Multiple IF Statements
Hello, I'm trying to write an IF statement that has 3 possible outcomes. I have written each statement on their own and they all work, but I can't link them together. What I would like to is: IF cell E42 = "3 Months" =IF(E42="3 Month", VLOOKUP(A45,Data!$A$2:$E$13,3,FALSE)) Else if cell E42 = "12 Months" =IF(E42="12 Month", VLOOKUP(A45,Data!$A$2:$E$13,4,FALSE)) Else if cell E42 = "24 Months" =IF(E42="24 Month", VLOOKUP(A45,Data!$A$2:$E$13,5,FALSE)) Any help would be great! Thanks Maybe... =vlookup(a45,data!$a$2:$e$13...

Formula?
Hi, I have my data set up the following way - the columns refer to th month and the rows are the different projects I'm tracking. Projec "A" has data in months Jan-Oct (10 values), while Project "B" has dat in months Jan, March, July, August & Sept (5 values)-the missing month are blank. Is there a way get the average of the last 4 values fo each project? Maybe using a count function? thank -- Message posted from http://www.ExcelForum.com Hi if your values are in A1:J1 use the following array formula (entered with CTRL+SHIFT+ENTER) =AVERAGE(OFFSET(J1,0,0,1,-...

shared calendars #4
Is there anyway to share a calendar and block out the Personal items? Thanks! Sounds like you're asking if you can share *part* of your Calendar, but not all of it. Unless there is a 3rd Party program, or a Script that would allow this, I don't think it is possible. Give http://www.experts-exchange.com/ a look over. The http://www.theofficeexperts.com/outlook.htm might be able to help too. If the personal items are a question of your integrity, then just get rid of them. GL RW <param@community.nospam> wrote in message news:OW6drXHIGHA.2472@TK2MSFTNGP10.phx.gbl... >...

Excel Charts #4
I have switched on the tracking option from Tools menu. Tracking is perfect but I am not able to make charts in this condition as becuuse the chart option icon is deactivated. I need both the options simultanously. Pl. advice. Hi, When you switch the tracking on it makes the workbook shared. Shared workbooks have restrictions. One begin chartwizard. XL: Commands and Features Unavailable in Shared Workbook http://support.microsoft.com/default.aspx?id=130560 I'm not aware of any work arounds :( Cheers Andy R Kartik wrote: > I have switched on the tracking option from Tools men...

Printing postcards in Publisher #4
Using Pub2002. WindowsXp. I am trying to print postcards using Publisher. The postcard comes 4/page and the page dimensions are 8.5 x 12. Postcard dimensions are 4.25" x 6". No borders. What's the question? -- Don Vancouver, USA "babygirlnerd" <babygirlnerd@discussions.microsoft.com> wrote in message news:A9E97C14-3115-4397-B389-C34CA7B68864@microsoft.com... > Using Pub2002. WindowsXp. I am trying to print postcards using Publisher. > The postcard comes 4/page and the page dimensions are 8.5 x 12. Postcard > dimensions are 4.25" x 6". ...

Testing #4
Can ...

Help #4
Hello all I moved mailboxes from Exchange2000 to 2003 yesterday, eversince when you check email in outlook it gots an error like: Cant find the object, but still gets your e-mails. In Exchange the error is ID no: c1050000 Exchange System Manager Source MSExchangeSA Category MAPI Session ID 9175 The MAPI call 'OpenMsgStore' failed with the following error... The pop3 service is broken down and I don't know what to do, it's been a long night. Help please Don't use POP3 when your on a MS network. Connect to the Exchange server with Exchange connector. Re-established the s...

Budget Question #4
In Money 2003 - if I budget an amount and do not use it up, does the excess go over to the next month. For example - if I budget $10 a month for eating out and only use $5 in January, will the budget then show that I have $15 in February to spend? Thanks. ...

performance issues when enabling tracking in crm 4.0
The last few days when people have sent larger emails (over 500 people) and used tracking in that email, the SQL server has a lot of locks and pegs the processors at 100%. When the emails are sent and then later set to be tracked I dont have those issues. Am I missing a setting or a way to throttle back the connections? Any ideas? ...

Where to write XQuery statements?
Where to write XQuery statements? For eg: doc("D:\\workArea\\Books.xml")/Books/Book/Name Shilpa wrote: > Where to write XQuery statements? > For eg: > doc("D:\\workArea\\Books.xml")/Books/Book/Name The .NET framework in version 1.x and 2.0 does not have XQuery support. SQL server 2005 has some support. I am not sure what you are asking. Are you looking for an editor to author XQuery statements? -- Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/ Yes, I am looking for the editor. Can you write these queries in the Query Analyser of SQL Server 200...

VLOOKUP #4
Hi, I need some help with a Vlookup that im trying to make for a list of "table array" names that i have in another column. The function needs to search the name of the table to work properly but it can not recognize the table name as a text or function constant...Im doing this to avoid writting function by function the name that each one needs.... Column A Column B 1 =VLOOKUP(Day+E9;TBL_one;2;"FALSE") TBL_one 2 =VLOOKUP(Day+E9;TBL_two;2;"FALSE") TBL_two ...

Combo Box #4
Hi, In my userform, i have Firstname: <DropdownList> Question: 1. If I want the value of DropDownList to be read from code, how can I do it via code by hardcoding the entries in combobox_change() function ? Private Sub ComboBox1_Change() .... End Sub 2. If I want the value of DropDownList to be read from Column A1:A10 (note there are redundant value, how to avoid ?) Example: A1: Johnson A2: Stephen A3: Michael A4: Daryl A5: Michael A6: Johnson A7: Rebecca A8: Johnson A9: Sally A10: Sally So the drop down list will act...

RM Statement won't print applied credit amount-V10
Hi Folks I need to print an RM Statement where the original amount of the credit document needs to print up. However, if the credit document has been fully applied then the record will print, but the value for the transaction is empty as the document has been fully applied. For my example I have not run the Pd Trn Removal process at all. I've simply captured 2 invoices and 1 credit note and I've fully applied the credit note. I've tried the Long, Short and Blank layout and there is no difference. I've chosen the option to Show Applied Payments and I've not cho...

How to assign templates to customer statement reports
I'm new to GP and I will be attending training soon, but I'm in need to create 2 statement report / templates. I have created or better yet modified the templates in report writer but I cannot figure out how to make them show in the application, I have assigned security access to the users. When I click routines / statements in the application I do not have access to selecting or assigning new statement ID (the options are grayed out. Thanks ...