|
Calculating Quantities In One Column By Prices In Another
|
|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Calculating Quantities In One Column By Prices In Another - Excel
|
View Answers
|
|
|
I'm trying to get this sheet to add up so that it takes the number of items for each item number, multiplies it by the unit price,
then returns the number for each department with a total that each area is spending.
Below is the sheet I'm working on. I'm just unsure how to go about addint up the column by the price for each area.
http://farm3.static.flickr.com/2551/...8ec8dff6_o.jpg
Thanks in advance for any help!
Similar Excel Video Tutorials
Add Certain Column Only
- See how to select varying columns for summing adding with: 1.OFFSET function 2.INDIRECT function 3.INDEX function We will see that ...
Similar Topics
Hi
I working on two sheets
sheet 1 has Name, area, Unit Price and amount ,
in the sheet 2 i have the customer ID .. i require the minimum unit price based on the name and area.
There are multiple prices for each name and each name repeats many times
How can this be done
Regards
Ranjit
I am having an issue coming up with a formula...I could have done this all by hand by now, but I know we will be using this in the future so I would like to have a formula that would make this really easy.
What I am trying to do is take my budget in cell H1 and see how many units I can buy of a certain product. Unfortuantely (for the formula), each unit gets a .2% discount more than the previous item. For example, the first unit is $25,000, but the 2nd unit is $25,000-.2%, and 3rd unit is $25,000-.4%.
What I have done so far is created a table with formulas to calcualte the price of each unit:
=B$2-((B$2*0.002)*$A3)
where cell B2 is the base unit price, and A3 is the number of units I have (because the supplier will continue the discount for as long as we by the items up to 100 units total (for a total discount of 20% for units 101 and later...but I don't need to worry about this because we will never hit 100 units so I am not adding this to my formula at all)).
So, column A is just a number from 0 - 100. Column B is the cost of each unit for Item #1 by using the above formula...Column C is the same for Item #2...Column D for Item #3...and Column E is for Item #4.
Now that I had the cost of each unit, I was highlighting as many as necessary until it goes over my budget and then manually count how many that is; and then do the same thing for 3 other units (different starting prices) and then report to management how many of each we could by with our budget. But as you can tell, this is just annoying haha. I would like to just update the amount of units we already have, enter my new allocated procurement budget, and have it tell me how many of each unit I could buy with that money.
So, in summary, I need a formula that takes into account how many units I have already purchased of a particular Item (entered into a cell, for example H5 for Item 1, H6 for Item 2, etc.), then calculates how many items we can purchase by taking the budget in cell H1 and dividing it by the sum of the cost of the next series of units and then tell me how many units we can afford in cell I5 --> I8
I know, it sounds kind of confusing. Sorry, I am trying to explain it best I can.
I have a spreadsheet that has columns for Item Description, Item Number, and Price. Every week the prices up date and I am using the next available column to put the new price in. The sheet looks like this:
A B C D E F
1) Item Des. Item # Price 11/5 11/12 11/19 etc...
2) x 1 2.00 1.00 2.00
What I want to do is have the last price that was entered in the dated columns to be the value in the price column. I tried this formula:
OFFSET(A2,COUNTA(D2,IV2)-1) and it returns the value in A2
Can anybody help?
I'm using Excel 2007 and I have an assignment I'm stuck on and was hoping I could get some help.
I need to write a simple VLookUp Funct but for some reason its not working.
I have two sheets, one is called Invoice which contains a sample invoice for a customer with item numbers, their description, quantity, unit price and extended (total) price. The second sheet is a product table that contains each items number and the information that would be filled in the invoice (ie. description and unit price).
So, what I need to do is write a LookUp funct that displays the description and unit price when the Item Number is typed in.
ex. If I type in Item Number 21, I need it to display "Product Name" and "Unit Price."
I tried "=VLOOKUP(A5,Product!A3:C9,(2,3),TRUE)" and it only displayed the Product Description name. How do I get it to show the Product Description Name and then Unit Price?
The way my workbook is setup is like this. There are two sheets, one that I paste a data list into that contains an item name, price, unit and sales total.
Code:
Column A Column B Column C Column D
Item Name Price Units Sales
The second sheet takes that data and creates a top 20 list based on most units. Currently I'm using a formula that uses the following functions:
1. Large - finds the first, second, third, etc.. highest unit total
2. Match - takes the large function result and finds the row #
3. Index - takes the match # for the row# and I change the column # to display the item name, price, units, and sales
The problem I've run into is that when items have the same unit total, the match function won't work properly. As an example, say the second and third highest unit totals were both 23. The large function will give a value of 23 for the second and third highest values. The match function will then go and search for the #23 and find the first instance of it. The match function will then display the same exact row # for the second and third highest value. I need a way for the match function to skip the first instance of the # if it had just found that number.
This is the formula I've go so far:
=INDEX('Data Sheet'!$A:$D,(MATCH(LARGE(IF('Data Sheet'!$B$1:$B$10000"",3),'Data Sheet'!$C:$C,0)),3)
This is finding the third largest number in column C, then finding the row #, then index looking up that row # in column 3 (i.e. C) which displays the third highest unit total. The IF function is in there to ignore any unit totals that don't have a value in the price column. This excludes the Grand Total row.
Any help in solving my problem would be greatly appreciated. Thanks
Okay,
Forgive me ahead of time, I really don't know much about Excel. I can work with templates and create simple formulas but alot of what I read here comes across as "Excelspeak" to me.
Here is what I want to do:
I have a price list sheet created. This price list has cells labled by name and then "duration", basically for every item there are four prices, based on duration of a rental or sale. I want to create a quote program that will pull data from the price list based on the quantities entered in the quote program.
For example
Price list is "System A, description, $1, $7, $14, $100"
Each of these items divided by commas is a different cell so my plan is to basically duplicate the price list on another sheet with no prices displayed but when I ented a number into the respective column for each duration the program pulls the base data and multiplies it by the number entered and only displays this number. Then of course there are sums and sales taxes etc but I think I can do that already.
Sorry, I know I'm not decribing this accurately but I hope you get the gist of what I am trying to do. I have tried to get "help" within Excel but, frankly I just don;t follow most of what is said.
Basically what I am trying to do is this: =('Price List'!$F$4)*F4
I know this is circular....I just don't know what to do about it.
Help
I have a price list that has the following headers for the columns:
Item- unit cost - regular price -dicsount count price - super discount price
For each item, I maintain 3 different prices depending on the types of customer. I have approximately 16 items in this list.
When my unit price changes, II would like a formula where I can manually change the unit cost, and then the three different prices for that item change automatically in the same amount that the unit cost changed.
Thanks
Hi,
I'm doing a pricing anlaysis and I'm trying to compaire actual purchase prices to the price the item should have been sold at. It becomes a little tricky becuase our pricing is volume based and there are 200 different items each with about 10 differnt price breaks (example for one item below)
Item
Qty
Price
132
1
$2.27
132
100
$1.69
132
250
$1.21
132
500
$0.90
132
1,000
$0.63
132
2,500
$0.33
132
5,000
$0.19
132
10,000
$0.14
132
25,000
$0.13
132
50,000
$0.12
132
100,000
$0.10
The item number is 132 the next column contains the quantitiy break, followed by the price at each break.
Is there a way to lookup the item and the purchase quantity and return the correct price for the item at the shipped quantity?
Example
quantity
item
price
1200
132
?
based on the table above and the two elements of data I would like to return a price value of .63 becuase the price would be .63 for all quantities between 1,000 and 2,499.
Any ideas are much appreciated.
Thanks!
John
I am trying to create an excel spreadsheet that will sum up the number of selected items and then generate a total that takes the item, times the price, and gives me a total price.
For example, if I have a pricing table of:
Item Price
Item 1 1000
Item 2 2000
In my data list if have
User 1 Chose item 1
User 2 Chose Item 2
User 3 Chose Item 1
I want to display
Item 1 has 2 items
Item 2 has 2 items
Total cost is 4,000.
This can be hard-coded, but at this time I do not know what items or users will be selected. I have specified a general example, but I have attached a very specific spreadsheet. My problem is the hard coding in cell F37 and I want to make this a dynamic formula. I am using Excel 2007.
I am doing a project. A sales invoice. I am using a VLOOKUP. I have item number in col A, Description in col B, Quantity in col C, Unit Price in col D and extended price in col E...... I have my description and Unit price working fine when I put in item number. The problem Im having is when I use the formula =C1*D1 in the extended price I get a #VALUE. But only if the Unit Price col is blank. If I insert a 0, then it works fine. But I dont want to have random 0's down the price col. Can someone please help me out asap. I can email you the file to look at if necessary.
Thank you very kindly,
Steve
EDIT: Sorry for cross posting. I didn't know these forums were linked.
Originally posted he http://www.mrexcel.com/forum/showthr...=1#post2411386
----------------------------------------------------------------
Hello all,
I'm trying to create an order form that is self calculating once a particular price level is chosen.
We have 4 price levels (A-D)for the same item depending on dealer's commitment.
What I'm hoping to do is create a formula where if I type in 'A' Price Level (or B,C,D), then the total $$ cell multiplies the QTY by the 'A' level price, to get the correct total for that line for the 'A' level price.
I've done this in the past, but haven't done it in many years, so I forgot how I accomplished it.
Please let me know if my explanation above isn't clear enough.
Thanks in advance!
Quote:
Originally Posted by csquare43
Here is a picture that may help someone help me out.
http://i34.tinypic.com/25plclx.jpg
So I'm selecting the price level in cell D3.
The price levels are defined in Columns G-J.
The Qty is entered in Column E & F.
I want cell H3 to select the correct price level, multiplied by the QTY's in Column E & F.
Does that make sense?
Thanks again.
Hello all,
I'm trying to create an order form that is self calculating once a particular price level is chosen.
We have 4 price levels (A-D)for the same item depending on dealer's commitment.
What I'm hoping to do is create a formula where if I type in 'A' Price Level (or B,C,D), then the total $$ cell multiplies the QTY by the 'A' level price, to get the correct total for that line for the 'A' level price.
I've done this in the past, but haven't done it in many years, so I forgot how I accomplished it.
Please let me know if my explanation above isn't clear enough.
Thanks in advance!
Hi,
I'm not sure how to pull this off. It's "simple" table with different prices for different number of people attending a trip. What I need is a column to input prices, which will then multiply with another column that contains a number of people. There are several columns with number of people, and I need a formula with variable in it (a number to multiply with price), so it would multiply that number of people with certain price. User want's to input number of people in header (first row) of that column, to see prices for each row in that column. I thought of variables, because he will input different number of people in each column, to list prices. Hope it's not confusing.
Thanks in advance!
Edit:
Here is my current table in attachment. Selling price needs to have it's own column, to multiply with number of people (pax columns). Number in yellow box, for pax columns, will have to be different in each column, not incrementing like columns in attachment. So each time user inputs number of people in that yellow box it multiplies with numbers in selling price column.
I am working a spread sheet that has two columns. Column A has prices, Column B calculates the change in prices of column A. So each cell in column B contains a forumla that calculates the change in price between the previous price in column A and the current price.
I need to calculate a large number or prices, so Is there a way to automatically, enter the formula into all of the cells in column B and make sure that that formula adjusts so each cell in B is calculating the change in price of the corresponding adjacent A cell?
Thanks!
Hi,
I have a sumproduct formula :
Code:
=SUMPRODUCT(SUMIF('Price List'!A2:A1400,Sales!C2:C5000,'Price List'!D2:D1400),Sales!H2:H5000)
This Formula gives me the sales amount depending on Price list sheet Whe
A2: A1400 is the item code in price list sheet
D4:D1400 is the price in price list sheet
C2:C5000 is the code for the sold items in Sales sheet.
H2:H5000 is the quantities of sold items in Sales sheet.
The good thing that I can add more criteria to this formula as getting sales for a specific company ....etc.
Now a new thing has come : sometimes a company issues a new purchase order for items with a different price .... I can add the same item code with this price to the price list ,and this P.O. number to sales ...How can I modify my formula to count the sales with items that have different prices for the the same product code?
Thank you
____________________________
Salah
Hi,
hopefully you guys can help me.
My tables:
Services (Service#, Service Name)
Components (Component#, Component Name)
Items (Item#, Item Name, Item Type, Item Price)
Component Composite (Component#, Item#, Number of Items)
Service Composite (Service#, Component#, Number of Components, Item#, Number of Items)
My queries:
Q1: Compontent-Item relation. (Component#, Component Name, Item#, Item Name, Number of Items, Expr:Total Price)
Q2: Service-Component relation. (Service#, Service Name, ComponentName, Item Name, Number of Items, Total Price)
This way I could display the name of the Service, it's components, the items within the components and the total price.
But a Service could contain not only Components with the items within, but also a single item next to the Component.
For example: Service 001 contains Component A, but Component A doesn't standard contains item 004. But Service 001 contains Component A and item 004. And this isn't working.
How do I have to set up my query? Do I have to make other sub-queries?
I hope my explanation and questions are clear. I'll be happy to explain if it isn't!
Thanks!
Phoenix
Please help me in finding the best alternative
Part 1
Product Cprice Hprice Mkt
Item 1 2.05 2 2.1
Item 2 #NA 2.5 2.45
Item 3 #NA 3 3.01
Item 4 6.5 6.52 6.48
Item 5 #NA 4 4.15
Part 2
C H Mkt Enter Final
x 2.1
x 2.5
x 3
x 3.05 3.05
x #NA
Basically, i send a sheet like the above, containing a few hundred items on it. Manager reviews it and makes price changes daily.
99% of prices will always stay the house price.
The issue is when the price choice changes to Market, Client or Entered Price.
I would like something that better displays the information, and most importantly, prevents error.
Column F, G, and H are the three current options for price, except when a price is entered into column I (that becomes the final price regardless of any Xs).
Issues arrise when
1) manager doesn't follow the lines properly, and enters a new price that is outside of "reason" with all three provided prices (which i have solved using a conditional format)
2) Manager leaves x on a field were no price exists (or was available as of that morning)
Any excel masters out there know how to make this better, or more userfrieldly?
Thanks in advance
Q
I have an inventory excel spreadsheet with about 2000 items in it. I use my own part numbers, item description, price, last date price was updated, and company purchased from.
To this point I would use the excel find feature and update pricing, date of price change that way.
I am trying to update the pricing by searching with my part number.
I have a new worksheet which has 3 lines.
Part number, item, price.
I would like to enter my part number, excel lists the item and current price. If I need to change the price I can change there and it changes it in my inventory. Right now I am using Vlookup to display the info now. Takes too long. My dilemma is changing the price and it changes the price in my inventory worksheet.
If possible I would also like to change the date on which I am making the change.
Thanks in advance.
Alright here's the deal. I have a sheet that has a list of items,
quantities, unit price and extended price. Right now they are numbered 1-20
but they are on row 11 - 30.
You can see a screen shot here to see what i mean
http://img134.imageshack.us/img134/2361/excelh.jpg
Well anyways, i want to see if anybody knows how i would be able to insert a
new line and put the corresponding number in there.
So lets say for instance i need 50 items instead of only 20. I wanted to
see if anybody knows how i would be able to write a macro where it will
automatically add new lines below 20, and go all the way through 50.
So after running the macro it will go from only being 20 to being 50 or
however many items are needed.
This will help me out a very lot and save me a lot of time if someone might
be able to assist me with this.
Thank you for your time and help.
Hi, Friends,
I'm new to excel and don't know how to explain my problem. Anyway I have a big inventory of 5000 items every item has seprate Abbriviation CODE (Taken out from its Brand, Item's name and size). Related to this CODE is the Description ,Unit, purchasing price ,Sale Price and supplier's name. All these items along with the details we have put in one sheet of Excel like below,
A1 B1 C1 D1 E1 F1
Item Code : Description : Unit : P-Price : Sale-Price : Supplier
PHEPSO4 HEP Socket 4'' NOS 12.00 13.20 HEPWorth
Now I want when I write the Code PHEPSO4 in another sheet having the same columns in the first column all the remaining columns are filled automatically (When I make an Invoice). Can Excel have this solution even if the Row number and the column numbers are different? Please, kindly help me. I will be really thankful.
Hi, I have a question that relates to calculating pricing where the price per unit rises in incremental steps as the number of units increases. i.e.
The price might be $0.75 per unit for 0-200 then $0.50 for 201-500 $0.35 for 501-1000 etc
I'm trying to work out how to enter a number of units ie 300 and by referring to a table of prices for each range of unit numbers the formula calculate that 200 will be at 0.75 and 100 will be at 0.50 and calculate a total cost of $250
Any help would be appreciated.
I have a column of prices as shown and want to be able to total the
entire column. Sometimes some of the individual items are "included"
and sometimes they are not. If they are not included in the main
price I need to include their individual prices in the column total.
If the items ARE included in the main price the word "included" will
appear instead of the feature's individual price, but I still need to
be able to total the column. The SUM function returns a #VALUE!
error.
Main price 240000
feature A 80000
feature B 9500
feature C Included
Feature D 93000
TOTAL 422500
How can I sum a column that has at least one number in it although
there may also me non-numerics in it?
Good morning
I have one sheet with a list of prices for items. I want it to pull those prices from another list depending on information from yet another sheet. Here's an example:
On the Information Sheet, I select State Farm.
On my Estimate Sheet, I have a list of items with pricing for each item.
In my price list, I have a list of those items, with different prices based on which company is used. So for instance, Column A is the list of items, Column B is the default price, Columns C-H are different insurance companies. Under each of those are prices for each of the items in Column A. In the estimate sheet, I want it to look up the prices based on what company is selected from the information sheet.
Is there a way to do this Thanks in advance.
Apologies if this is a simple query, I have struggled to find an obvious solution on the wider web, though.
I am trying to calculate the median sale price across a total number of sales. Total 2046 sales at 17 different prices.
The data is in two columns. The first column contains the unit price. The second column contains the number of sales of that unit at that price.
Data in the attachement.
Any assistance appreciated.
Hi, I am an Excel virgin, almost, and need help to do the following in XL2003.
I want to be able to enter two dates say in cells A and B, cell C to show the number of days and cell D to show the total price of items sold. Items are sold every day of the year and the price is seasonally adjusted but fixed for each month. Prices are different each month. So I want to be able to enter e.g 11/12/2007 in A 23/05/2008 in B and see the number of days in C and the total price in D automatically calculating the various prices per day for each month which are elsewhere on the worksheet.
Any help would be much appreciated. Thanks.
|
|
