Small Business Formula For Price List
I need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins. Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Lookups For An Item's Price From A Price List
The analysis basically has 2 data components to it: The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time. The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased. What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below: 1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2) 2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least) 3) Pull the price for this "closest quantity" I have uploaded a worksheet showing the structure of that data. [url] Is there some VB code I need to do this, or can it just be a few simple formulas?
View Replies!
View Related
Building A Formula To Increase Price List 8%
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View Replies!
View Related
Price List
i have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if i want to intersect all this and find the price how can i do so
View Replies!
View Related
Formula To Generate 3-5 Business Days
we have salespeople in all 50 states and each state uses different rules for business days. Some states include Saturday as a business day, some don't, and Alaska uses 5 business days including Saturdays. We are open 7 days a week. What I am trying to create is a worksheet that has each day of the month going down and in the cells next to it, a column for states that include Saturdays as business days; so a date 3 business days out would appear in the next cell. In the cell after, a date 3 business days out not including Saturdays. And in the last cell for Alaska, a date 5 business days out including Saturday. Of course federal holidays are all excluded. Something like this: March w/ Sat w/o Sat Alaska 1 5 5 7 2 5 5 7 3 6 6 8 4 7 7 10 5 8 10 11 Now Vermont has extra holidays certain months so I would have to adjust that in the formula for the middle column during those months.
View Replies!
View Related
Price List Lookup
I have a price list Width/Drop 100013001600190022002500280031003400370040004300460049005200 850 1150 1450 1750 2050 2350 2650 2950 3250 3550 3850 I need to index given :- My ranges are named Width and drop and List. Rules are anything above largest drop/width must return 0. anything below lowest width/drop will be the lowest listed anything inbetween will choose the value >= to the value. lookup is returning the closest, and my other formula fell foul of nesting, i am working in 2007 but it is targeted at XP/2002.
View Replies!
View Related
Update Price List
I have a price list with part numbers in Column E and prices in Column C. I want to update the prices from a master list that has part numbers in Column D and prices in Column H and then make only the updated prices bold. Currently the master list is in a different workbook, but if I need to, I can copy and past the master sheet into the same workbook as the sheet I want to update.
View Replies!
View Related
Adding Business Days To A Date Formula
I am looking for a formula that looks at a date and could add business days to it. for example: If the date in a field is Monday, 15-dec-08 and my formula is to add 5 Business days (mon-fri) to it not including that date. The desired result would be Monday, 22-dec-08 but my formula gives me 20-dec-08
View Replies!
View Related
VLookup: Old Price List Update
A B C D 1 123 1.99 123 2.09 2 124 3.99 124 4.09 New prices arrived as shown in column C (code) & D (price) I want my old price list updated A (code) and B (old price) Can VLookup use the new data and replace B with information from column D.
View Replies!
View Related
Multiple Pictures In A Price List
Hi, I've got a price list with 2500 different lines which the boss has decided need a picture against each one (the guys a legend!!). Can anyone help me with a macro that will look up the code in Column A, then add .jpg on the end, and insert a picture into Column J?
View Replies!
View Related
Price List Add 20-30% And End In 99cents
I get a price list from my distributor, my scale I use for my store is anything below $10 (from my distributor price) gets 30% markup, then anything above $10 gets 20% markup. How would I be able to function this into the list I receive in excel so I can export the prices directly to my store of what the prices should be in my store. Also it would be a plus if at the end I can get it to end in 99 cents. Thank you look forward to your response! I am using Excel 2003
View Replies!
View Related
Add 5% To Many Price List's Macro
I have around 150 separate price list files and i would like to create a macro to findformat currency then copy 1.05 and use Edit | Paste Special (value, multiply)...... then round up or down to nearest cents .567 would roundup to .57 The findformat and Paste Special will work when i manually do it.... but when i record as macro it will not work when played back...... Here is the code it records... code does not include the roundup part. I dont know how to do that. Range("M3").Select ActiveCell.FormulaR1C1 = "1.05" Range("M3").Select Selection.Copy Application.FindFormat.NumberFormat = "$#,##0.00" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Range("M3").Select Application.CutCopyMode = False Selection.ClearContents End Sub
View Replies!
View Related
Macro: Small & Large Formula
I wondered if there is a possibility to make this Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)" Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)" more simple so i can get the range for my small and large formula's variable? What i try to reach here is: Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp)) Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
View Replies!
View Related
INDEX SMALL ROW Formula Showing #REF!
I have the formula (found in cell "C2") on the Report sheet. I need to perform a function, but I cannot get it to work on the sheet I need to pull information from. The sheet RecapWk12 has a small section pasted (with some cells edited for obvious reasons) from the actual workbook. I can get the formula in Report cell (A10) to work on pulling information from sheet2. You can see I am getting (#REF!) in cell C2.
View Replies!
View Related
Figuring List Price - Cost To Show Discount Percentage
List Price $46.98 (e2) Net Cost $19.53 (e3) How do I enter a calculation that will show me my discount percentage from my supplier? (e4) I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View Replies!
View Related
Extending Vlookup: Price List To Cover Up To 10,000 Lines
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
View Replies!
View Related
Price List Lookups And Additonal Calculations For Out Of Range Values
The sheet has a price list (I attached the sheet). its a width x height(drop) format. If width or height <= minimum width/height then use the minimum listed. if width or height > minimum <=maximum then lookup in table next heightest value. here is the complication. any oversized items are priced as roundup((size -biggest size) / (biggest - second biggest size),0) * ( price of biggest-price of second biggest). so if my widths are 4600 4700 4800 10 25 35 and I am pricing 5050 I would do :- calculate howmuch its oversize 5050-4800 = 250 Calculate the difference in the last 2 sizes 4800-4700 = 100 Calculate the rounded up multiples 250/100=2.5 rounded up = 3...........
View Replies!
View Related
VLOOKUP Formula For Price Sheet
The task: create price sheet that calculates pricing based on 2 criteria - quantity, and production time. There are two worksheets. #1 is the main calculator, #2 is the price sheet, broken down with time (in hours) on the left column B, the quantity across the top in row 1. There are then boxes with different prices based on both the hours and quantity of products. On worksheet 1, I have specified quantity in C6, and time in C8. How do I pull a price in to F8 that calculates based off the time and quantity filled out on sheet 1?
View Replies!
View Related
Delete The Formula In The Amount The Unite Price Comes #value
(To keep things simple from left to right Column A-H) The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed. I have a vlookup formula in Column F (thank you VoG) =IF(ISNA(VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)),"",VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)) To pull prices from another worksheet.
View Replies!
View Related
Manually Enter % And Update Price (Formula)
I'd like to be able to add 10% to column D and have the prices in A, B, C change accordingly. Is there a formula for this? A B C D 2009 Distributor CASE Price2010 Distributor CASE Price2010 Distributor EACH PriceVariance from 2009 price135.00148.000.0592108.25100.000.040062.8875.006.250066.6096.008.0000 PS: Right now I have it set up working the opposite way, the prices are entered and my formula tells the user by what % the price has changed. The user wants to be able to tell the formula what % is desired and have the prices change accordingly. The formula I am using for the way column currently calculates is: =IF((G2=0),"",(((G2-F2)/F2)*1))
View Replies!
View Related
Formula To Calculate Price Based On Sheet2
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged. Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution. Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View Replies!
View Related
IF Formula Used To Price Life Insurance Policies
I price life insurance policies and need a formalu for the following new Fund: If the sum of the first two years of premium added to 10% of that total is equal to or less than 25% of the face amount, the case fits into the general parameter. Here is what I have, but it isn't working: =IF((U2+V2)*10%)+(U2+V2))
View Replies!
View Related
Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current) Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
View Replies!
View Related
Looking For The Closest Price To A Reference Price
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price? I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances. I would also like to reference the source on the next column.
View Replies!
View Related
Multiple IF's And AND Inclusive Formula (all In One Cell) That Would Look At The Above Table And Depending Upon The Price Paid
.............................24............30............36 300014999..........9.00%.......11.00%.....12.00% 1500099999........9.50%.......11.50%.....12.50% 100000249999.....9.00%......11.00%.....13.00% I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
View Replies!
View Related
Making Average Buy Price And Average Sell Price
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View Replies!
View Related
Calculating Business Hours
I use this formula at work to calculate business hours from Mon-Fri: =IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,$N$2:$N$23)-1)*($Q$3-$Q$2)+IF(OR(ISNUMBER(MATCH(INT(L11),$N$2:$N$23,0)),WEEKDAY(L11,2)>5),$Q$3,MEDIAN(MOD(L11,1),$Q$3,$Q$2))-IF(OR(ISNUMBER(MATCH(INT(L10),$N$2:$N$23,0)),WEEKDAY(L10,2)>5),$Q$2,MEDIAN(MOD(L10,1),$Q$3,$Q$2))) where Q3= business start time 8.30am where Q2= business end time 5.30pm thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours. I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.
View Replies!
View Related
Calculation Of Business Hours
I am using the following formula to calculate business hours. =(NETWORKDAYS(R9,T9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00") The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g., Condition 1 When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00" Condition 2 While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".
View Replies!
View Related
Return The Corresponding Business Week
I have a spreadsheet (attached) containing 3 columns, week commencing (c), week ending (d) and business week (e). The question is can a user enter a date in one cell (b4) and have the next cell (b5) return the corresponding business week, I have come up with a couple of solutions involving hidden columns but was wondering if there is a way to do all of this in one cell.
View Replies!
View Related
Caluclating Business Days
I am looking to calculate business days - more specifically Monday through Friday. I am not currently worried about holidays or vacations, yet I wouldn't mind including it if I could have a list to "check" from. I tried previous searches, but found a few functions that I don't have on my computer.
View Replies!
View Related
Calculating Business Fiscal Year
writing a formula that would result in my organizations' business fiscal year. Assuming the fiscal year is 2008, the quarters are as follows: 1st QTR = 7/2007 - 9/2007 2nd QTR = 10/2007 - 12/2007 3rd QTR = 1/2008 - 3/2008 4th QTR = 4/2008 - 6/2008 When 7/1/2007 is entered, the result should be Q1-2008 10/20/2007, Q2-2008 and so forth.
View Replies!
View Related
Adding 1 To A Number On Business Days
I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.
View Replies!
View Related
SMALL Function
I'd like to use the SMALL() function in excel to pull out the second lowest unique value in a list, but I'm not sure there is a way to do this. For example, if the array is {1,1,3,10,2,6}, then SMALL(array,2) returns 1, but I'd like it to return 2. Is there a way I can modify this function or use a different one to achieve what I want?
View Replies!
View Related
CALCULATION TAKING BUSINESS HOURS INTO CONSIDERATION
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete. I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays. here are some examples. from - 2/12/2004 13:00 (thursday) to - 2/13/2004 9:00 (friday) answer should be 5 hours from - 2/13/2004 14:00 (friday) to - 2/16/2004 10:00 (monday) answer should be 5 hours
View Replies!
View Related
Calculating The Number Of Business Days In A Specified Period
It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g Mondays, Wednesdays and Fridays of June June 1, 2006 June 3, 2006 June 6, 2006 Tuesdays, Thursdays and Saturdays of June June 2, 2006 June 5, 2006 June 7, 2006 This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months
View Replies!
View Related
Userform VBA Which Will Determine Business Days
I have created a useform in VB which will determine business days. I need help in the coding part. In my user form I have text box, I have a calender and I have two command buttons, 1 Calculate 2 exit. What i would like to achieve is when i type a number in the txtbox and press cmd button calculate the code will shade the the date in the calender so for example if I type in 10, then the code should shade 13/02/09 on the calender since 13/02/09 is the 10th business day.
View Replies!
View Related
Time/date Tracking Only During Business Hours
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
View Replies!
View Related
Small / Min Function
I have the G column listed with number from 1-100. On the B column I have the corresponde titles for each number in G. What I want to do is =Look at the G(1:100) take the top 10 lowest value and write the correspondant title nearby. I think it s possible with Min or Small functions, but I dont know how to, especially taking the title names nearby.
View Replies!
View Related
If Greater Than Or Small Than, Or Equal To
I have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.
View Replies!
View Related
|
TRACKER
