Small Business Formula For Price List

Jul 21, 2006

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.

ADVERTISEMENT

Building A Formula To Increase Price List 8%

Sep 27, 2006

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 2 Replies View Related

Lookups For An Item's Price From A Price List

Jan 4, 2010

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 4 Replies View Related

Checking Contents In Small List Against Big List?

Dec 2, 2011

I have a column, lets call it A, with a small list of airports. There will never be more than 7 airports in this list. There may be as few as one airport.

Now, I have column B which has ~100 airports listed.

All involved airports will be listed by their 4-letter code, i.e. PANC, PADG, PABE, and so on.

What I want to do is, if any airport in Col A is also in the list in Col B, to have that airport populate into a cell, like C1. Now, if two airports in A are also in B, then those airports should populate into C1 and C2, and so on for 3 or more airports.

I could probably do this by nesting 7 IF formulas to check each line one by one but, there's got to be a more elegant solution that can handle this?

If VBA is needed, that's fine too as this project already has macros in it.

View 1 Replies View Related

Excel 2010 :: How To Reference Unit Price In List Two Based On Date In List One

Mar 8, 2012

I'm using Excel 2010 on a Windows 7 machine.

I have one sheet with a large list of dates and total prices.

I have a second sheet with a list of dates and unit prices.

I want to divide the total price in my first list by the unit price on my second list.

I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.

My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.

View 2 Replies View Related

No SMALL And LARGE With SUBTOTAL On Filtered List?

Oct 17, 2011

I have sales data in columns, the right most column has values in it.

If I filter the data using the standard Filter, I can add only filtered records by using SUBTOTAL(9, filtered_list) and this gives me the answer.

How can I find the 2nd largest or second smallest ONLY from the filtered data without using a Pivot table?

View 5 Replies View Related

Excel Sheet That Will Separate List Into Small Groups

Aug 24, 2013

I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.

This grouping would be repeated 4 times, but there are some conditions.

Firstly, the same person cannot be put into a group again with someone they have previously been grouped with.
Secondly, someone appearing in the first or last groups cannot appear in that group again.

I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.

I attached an example dataset : demo dataset.xlsx‎

View 2 Replies View Related

Formula To Generate 3-5 Business Days

Mar 28, 2008

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 9 Replies View Related

Price List

Sep 16, 2009

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 9 Replies View Related

Adding Business Days To A Date Formula

Dec 8, 2008

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 6 Replies View Related

Price List Lookup

May 8, 2009

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 5 Replies View Related

Price List Add 20-30% And End In 99cents

Jan 4, 2010

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 12 Replies View Related

Update Price List

Feb 1, 2007

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 9 Replies View Related

Add 5% To Many Price List's Macro

Apr 28, 2007

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 5 Replies View Related

INDEX SMALL ROW Formula Showing #REF!

Dec 10, 2008

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 3 Replies View Related

Creating Loop With Small Formula?

Mar 13, 2014

I am trying to get the following thing started but my loop doesn't succeed. I've got 3 columns. I need to place a formula in column C if A is empty.

Example:

A2 contains data so do nothing.
A3 is empty do: C3=B3+B4.
A4 contains data so do nothing.
A5 contains data so do nothing.
A6 contains data so do nothing.
A7 is empty do: C7=B7+B8.

this is what I've got so far:

[Code] ..........

View 4 Replies View Related

Macro: Small & Large Formula

Dec 6, 2006

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 6 Replies View Related

Find Latest Price By Each SKU In The List?

Dec 24, 2013

How to find the latest price by each sku in the list, the list contains many skus.

View 13 Replies View Related

Ascending Order Of A Price List?

Apr 8, 2014

formula getting an ascending order from a long list of numbers.

I use excel for mac, 14.3.9

View 7 Replies View Related

VLookup: Old Price List Update

Dec 10, 2009

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 14 Replies View Related

How To Use Dropdown List To Show Price

Dec 31, 2012

I have a price list with type & price, I want do a drop down list for type (this is solve), so when i select list at type, the cell I want it show price.

View 2 Replies View Related

Add Set Amounts To Price List With Different Criteria?

Oct 24, 2013

I need to add different amounts to a column of prices based on a column of zone numbers. I don't know if that makes sense but here is an example test.xlsx

View 3 Replies View Related

Multiple Pictures In A Price List

Oct 6, 2008

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 11 Replies View Related

Macro To Compare 2 Price List

Apr 11, 2012

I am looking for VBA Macros to compare 2 price list. I am trying to achieve the following.

1. Script look in sheet one i.e. Old list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
2. Script look in sheet two i.e. New list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
3. Script look in both the sheet for duplicate values it it is there then it should display in 3rd sheet i.e. duplicate

Also it should count the number of records on each sheet in column F1. If possible can we display the work % while checking both the lists.

Sample file is attached.

View 9 Replies View Related

Notation Of Formula With Range (small Question)

Jul 28, 2009

Ok, I want to use a dynamic range in my Vlookup formula.

I want to use this formula: ....

View 11 Replies View Related

Using Price List To Populate Purchase Order

Mar 1, 2013

I have been sent a price list from a supplier and need to create an order form that can be populated from the price lists. I have to pages of price list as well. I will also be adding to these as I start getting other supplied product and need my client to be able to generate a order from these in the simplest way possible. Again I hardly use excel and these docs are all sent in excel format.

View 4 Replies View Related

Auto Complete Items Off The Price List

Jul 2, 2008

I am trying to create an order form. i have a price list from my local hardware store that i want linked to my order form. i want my order form to autocomplete items off the price list. i have tried a few things but im stumped.

View 9 Replies View Related

Index Small Formula Takes Forever To Save?

Sep 15, 2014

I have one data tab which constantly changes. (adding more products and corresponding volume/prices) For the lookup tab, I want to look up one value and return multiple values.

I have figured out an index array formula but it just calculates so slowly. My live file is about 8MB now. When I apply my formula, it takes forever to save.

View 7 Replies View Related

Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies View Related

Calculate The Implied Volatility Which Minimizes The Sum Of Squared Differences Between The Observed Market Price And The Model Price For Each Day

Jun 9, 2008

I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.

View 2 Replies View Related

Extending Vlookup: Price List To Cover Up To 10,000 Lines

Feb 9, 2009

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 5 Replies View Related

Price List - Detail In Tabular Form With Different Criteria

Sep 16, 2009

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 9 Replies View Related

Price List Lookups And Additonal Calculations For Out Of Range Values

May 11, 2009

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 3 Replies View Related

Figuring List Price - Cost To Show Discount Percentage

Apr 25, 2006

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 9 Replies View Related

Create Auto Updating Price List Using Part Number And Website

Jan 3, 2014

I'm trying to create an autoupdating price list using a part number and a website. I've tried Excel's data import wizard.

Website: WebFLIS - Public Search
Sample data Category
Chaplain Kits
Item
Chaplain's Kit, Consumable
NIIN
9925-01-326-2855

Price
$276.94
Description
Also called the ReSuppply Kit

VBA Code that allows me to automatically open the page.

Dim IE As Object
Sub submitFeedback3()
Application.ScreenUpdating = False
Set IE = CreateObject("InternetExplorer.Application")

[Code] ......

Ideally, I'd like to click a button that says "Update Prices" and it will search WebFlis for the NIIN listed and update the price. I have 717 items on my list so updating would be by click only (I think I can write that portion).

If that is not an option, I'd like to be able to click on each item row (think hyperlink) and be able to see the results for that individual item.

View 9 Replies View Related

Price Curve Formula

Apr 9, 2009

I'm trying Calculate Curved Price Rate by SF. phil.freestyle@gmail.com

View 5 Replies View Related

VLOOKUP Formula For Price Sheet

Jan 27, 2010

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 4 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)

Jul 24, 2006

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 7 Replies View Related

Formula To Calculate Price Based On Sheet2

Apr 23, 2009

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 9 Replies View Related

Manually Enter % And Update Price (Formula)

Jun 9, 2009

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 9 Replies View Related

IF Formula Used To Price Life Insurance Policies

Jun 20, 2009

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 9 Replies View Related

ADVERTISEMENT