 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Text=Number
I have an Time and Material Calculator that I have made. I would like to be able to asign a number to a letter. Example if I enter "C" into a column I would like it to represent .001. If I enter "M" into the same column I would like it to represent .0001, and if I enter "E" it would represent 1. When I get pricing for my materials they come per 100, 1000 or each. I would like to just enter the pricing, unit of measure and have it calculate the per item pricing. Example, widgit A cost 80 per 100, I would like to enter 80 and a C and have it calculate that it cost .80 per widgit. Right now it looks like this Quan Item Price Unit Each Cost 2 Widgit 80 C .80 1.60 At this time when I get the updated pricing I have to go through and change the "price" and "each" column every time I get updated pricing. I would like to be able to just change the "price " column. I hope I exlplained this right. Thanks for any help! Hi Chris In E2 enter =IF(D2="",0,IF(D2="C",B2*.001,IF(D2="M",B2*.0001,IF(D2="E",B2,0)))) I have used 0 if D2 is empty or it is any letter other than C, M or E because you are multiplying E2 by A2 in cell F2 If we used Null "" you would get a #VALUE error in F2  SignatureRegards Roger Govier > I have an Time and Material Calculator that I have made. I > would [quoted text clipped - 22 lines] > I > hope I exlplained this right. Thanks for any help! Thanks! That did it, I had to change the "B's" to "C's" so it looks like this =IF(D3="",0,IF(D3="C",C3*0.001,IF(D3="M",C3*0.0001,IF(D3="E",C3,0)))) my B column is the item discription. This is working out better than expected, companies charge thousands of dollars for a program like this and I will be able to build this in a weekend and be done with it. I was trying to use the "if" function and it was not working correctly. Once again, thank you! > Hi Chris > [quoted text clipped - 30 lines] > > I > > hope I exlplained this right. Thanks for any help! Hi Chris, thanks for the feedback. Apologies that I used B instead of C, I could see quite clearly that B contained description. Put it down to brain fade!!! SignatureRegards Roger Govier > Thanks! That did it, I had to change the "B's" to "C's" so it > looks [quoted text clipped - 51 lines] >> > I >> > hope I exlplained this right. Thanks for any help! Chris In E4 enter =IF(D4="C",C4*0.01,IF(D4="M",C4*0.001,IF(D4="E",C4*1,0))) I think that your decimals were wrong - this give the same result as you showed Regards Peter > I have an Time and Material Calculator that I have made. I would > like to be able to asign a number to a letter. Example if I enter "C" into a [quoted text clipped - 13 lines] > pricing. I would like to be able to just change the "price " column. I > hope I exlplained this right. Thanks for any help! Peter, You are right I did have the decimals wrong, that could have cost a few bucks to figure out later. Thanks! > Chris > [quoted text clipped - 25 lines] > > pricing. I would like to be able to just change the "price " column. I > > hope I exlplained this right. Thanks for any help! Chris Your welcome - thanks for the feedback Regards Peter > Peter, > [quoted text clipped - 30 lines] > > > pricing. I would like to be able to just change the "price " column. I > > > hope I exlplained this right. Thanks for any help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.