Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Calculating Quantities In One Column By Prices In Another

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

Helpful Excel Macros

Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Filter Data to Show the Bottom X Number of Items in Excel - AutoFilter
- This free Excel macro filters a data set to show the bottom X number of items from that data set. This macro is really
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

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


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


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


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




Hi,

I have a sheet with 3 columns. Job, Work, Price. I have a 2nd sheet with 2 columns where one is a list of different job types and the other is their relevant prices.
Job - drop down list based off Sheet 2
Work - number
Price - number
What i want to do is create a formula where the price on the first sheet if automatically populated based on the job type and the number in the Work column.

So the second sheet contains, basically; (a vlookup called price)
*first line is blank
Job 1 - 50
Job 2 - 60
Job 3 - 70
Job 4 - 80
Job 5 - 90

What i need is if on the first sheet Job = Job 1 or Job 2 or Job 3, then just the price is displayed (the number in the work column has no factor)
And if Job (sheet 1) = Job 4 or Job 5, AND the number in the Work column is less than 5 then just the price is displayed
BUT if Job (sheet 1) = Job 4 or Job 5, and the number in the work column is 5 or more then an additional 12 is added to the price for each one over 4 (i.e. if it was 5 then 12 is added, but if it's 6 then 24 is added)

I had this, but it's not working and i can't think where i've gone wrong. Because parts of it work separately.
=IF(A2=OR(Sheet2!A2,Sheet2!A3,Sheet2!A4),VLOOKUP(A2,prices,2,FALSE),IF(AND(A2=OR(Sheet2!A5,Sheet2!A6),B2<5),VLOOKUP(A2,prices,2,FALSE),VLOOKUP(A2,prices,2,FALSE)+((B2-4)*12)))

Edit 1;
I think it's the OR part where there's a fault

Edit 2;
It was the or - needed to be before the A2=
=IF(OR(A2=Sheet2!A2,A2=Sheet2!A3,A2=Sheet2!A4),VLOOKUP(A2,prices,2,FALSE),IF(AND(OR(A2=Sheet2!A5,A2=Sheet2!A6),B2<5),VLOOKUP(A2,prices,2,FALSE),VLOOKUP(A2,prices,2,FALSE)+((B2-4)*12)))

Edit 3;
Ok that hasn't worked either - as it's removing the 12 when the value in Work is less than 5 (takes off 12 for 3, takes of 24 for 2, etc)

Edit 4;
Needed $
=IF(OR(A2=Sheet2!$A$2,A2=Sheet2!$A$3,A2=Sheet2!$A$4),VLOOKUP(A2,prices,2,FALSE),IF(AND(OR(A2=Sheet2!A5,A2=Sheet2!$A$6),B2<5),VLOOKUP(A2,prices,2,FALSE),VLOOKUP(A2,prices,2,FALSE)+((B2-4)*12)))



Good afternoon,
it's my first in the forum.

I have one calculation sheet with:
- lines/column A: containing the items descriptions,
- Column B: quantities
- Column C: Unit price (Offer 1)
- Column D: Total price (offer 1)
- Column E: Unit price (offer 2)
- Column F: Total price (offer 2)...

In total I have 200 lines and 5 offers (10 columns)

Actually, I arranged the formula for the 1st offer as follow:
D2=B2*C2,
D3=B3*C3,...

Normally, I must have for the 2st offer:
F2=B2*E2
F3=B3*E3,...

But when I I copy the formula from D2 in in F2 I get F2=D2*E2 ...
the indice of the quantity column is incremented by 1

how to solve the problem?
it's extreamly urgent.

thanks you in advance



I have a list of products that need their prices updating. How do I update all price fields to be 10% more than what they were before.

Or how do I copy the prices to another field beside the original price to it new price row for each item. So it is a static entry, but putting in a1 =sum(b1+10%) for each row. as the price will not be static that way. Once the price is updated to the new column I will be deleted the old price column.

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


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.


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 creating a system for calculating prices.
I need a function which calculates the price, according to which item is bought, and how many.
Items are chosen from a list which I have created.

Column A = Items
Column B = Prices

D2 = Item (chosen from list)
E2 = How many
F2 = Price

Let's say one item is (chosen from list in Column A) "Teddybear". The price is 12,99$.

I want to calculate the total sum, respective to the chosen item.

So if D2 = Teddybear, take price of teddybear (defined in the cell next to item), times the amount.

Tried to explain in picture :-D

Untitled.jpg

regards,



Hi. This is my first time posting, so please be gentle. I've been working on this workbook a past employee made. It's extensive but here's the jist: I have a bidding template workbook with part numbers (roughly 20,000+) in it. I have created a formula to search on a separate workbook which has updated prices from our supplier. My formula searches for the part number in the Prices workbook and returns the new price if it is found, and if it is not found on the new price list, returns the old price. In the Prices workbook, I created a formula for finding the "price per unit" as some are per 100 (C), per 1000 (M) or per each (E) as we bid per each unit. I want to add conditional formatting to the Price workbook that says "if the value of cell A2 came from the 'old prices' sheet/range, turn red, if the value of cell A2 came from the 'new prices' sheet/range, turn green. This will tell me which price is up to date and which is not. My CF works great, but I cannot seem to transfer this CF to my bidding template workbook when it looks up the value, it doesn't return the CF I created in my Prices workbook. I am the only one to alter the formulas, so in my bidding workbook I want it to return the value in the color the CF found as I am going to copy/paste just the values so when someone uses it, they don't copy/paste the formula, just the value (they don't need to see the formula or alter it, they just need the value and it needs to be a simple copy/paste not special paste). I have an example workbook attached. In this workbook there are old prices, new prices, up to date prices and MRO (material request order). The up to date prices sheet searches for the part # on old prices and new prices and returns per formula. Also, a separate column on up to date prices sheet to figure the price per unit. Then the MRO looks to the up to date prices sheet and returns the value. I'd like that MRO sheet to have the conditional formatting set in up to date prices sheet. Let me know if this is confusing or you need more information. Thank you for reading my "jist" which is not so much a jist at this point!

FOR EXCEL FORUM QUESTION.xlsx

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.


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


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,

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


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.


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


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.