Using MS Excel 97.
Need help with writing a formula.
I have the following three (3) columns in a worksheet used to calculate labor-cost estimates for construction.
Emp Type
Hours
Amt
There are twenty (20) employee types (Emp Type). Each has a different hourly pay rate. Three (3) examples are shown. The employee types are designated using a coded combination of letters and numbers. This "code" is necessary for employee privacy purposes.
GF = General Forman @ 41.06 per hourly rate.
FM = Forman @ 39.51 per hourly rate.
LAB = Laborer @ 17.11 per hourly rate.
I wish to simply enter in the first column the >>Emp Type<<. Into the second column the >>Hours<<. The >>Amt<< colunm will calculate and display the result.
It seems to me that giving the >>Emp Type<< a "name" that refers to the hourly rate is the simplest result. How can I do this? I have tried the Insert, Name, Define menu options to no avail. I continue to get a #VALUE! error message.
Thank you,
BWray
Hi,
I pasted 2 related tables below and I need to create 2 array formulas:
1) In Table 2 under "units sold" a formula in that returns the number of units of a particular product sold that has a given unit cost. It shows that 2 units with the serial no. 7001 costing $156 each were sold. Then I need to copy this formula down to the rest of the cells in the units "units sold column". As a result, the second cell down would show that 4 units of 7001 costing $142 each were sold, etc. This formula will also produce a result in the "Cost of Goods Sold" column that reflects the total fcost for those 2 units sold, $156 * 2 = 312
2) a formula in "prices column" (also in table 2) that returns the cumulative selling price obtained for those units sold. For example, relative to the first cell in this column, for the sales of 2 units of no. 7001 costing $156 each, a total price of $388 ($199 + $189) is obtained. Then I need to copy this formula down the rest of the "prices" column.
I have been racking my brain on this problem for 2 days and I am really desperate. ANy help would be greatly appreciated.
Thanks in Advance!
Jenny
Table 1. Sales Records
Product Name ID Serial Number Unit Cost Selling Price
Kodak EasyShare P850 7001 5244270 156 199
Kodak EasyShare P850 7001 3058375 156 189
Kodak EasyShare P850 7001 4786766 142 177
Kodak EasyShare P850 7001 6982965 142 177
Kodak EasyShare P850 7001 9889905 142 165
Kodak EasyShare P850 7001 4366908 142 165
Kodak EasyShare V70 5656 5646262 201 220
Kodak EasyShare V70 5656 6247505 199 234
Casio Exilim EX-Z850 9812 2846843 178 241
Casio Exilim EX-Z850 9812 3671198 178 241
Casio Exilim EX-Z850 9812 8447425 178 225
Sony DSC-N1 7753 261209 234 267
Sony DSC-N1 7753 5891987 222 258
Pentax Optio W10 2387 839551 256 305
Pentax Optio W10 2387 1281852 233 301
Pentax Optio W10 2387 2498589 233 299
Pentax Optio W10 2387 9939138 233 267
Panasonic Lumix 4444 9347263 300 388
Panasonic Lumix 4444 498636 300 355
total 4673
Table 2. Cost of Goods Sold Computation
Product Unit Units Cost of Goods
ID Cost Sold Sold Prices
Kodak EasyShare P850 7001 156
Kodak EasyShare P850 7001 142
Kodak EasyShare V70 5656 201
Kodak EasyShare V70 5656 199
Casio Exilim EX-Z850 9812 178
Sony DSC-N1 7753 234
Sony DSC-N1 7753 222
Sony DSC-N1 7753 200
Pentax Optio W10 2387 256
Pentax Optio W10 2387 233
Panasonic Lumix 4444 300
Hi,
I pasted 2 related tables below and I need to create 2 array formulas:
1) In Table 2 under "units sold" a formula in that returns the number of units of a particular product sold that has a given unit cost. It shows that 2 units with the serial no. 7001 costing $156 each were sold. Then I need to copy this formula down to the rest of the cells in the units "units sold column". As a result, the second cell down would show that 4 units of 7001 costing $142 each were sold, etc. This formula will also produce a result in the "Cost of Goods Sold" column that reflects the total fcost for those 2 units sold, $156 * 2 = 312
2) a formula in "prices column" (also in table 2) that returns the cumulative selling price obtained for those units sold. For example, relative to the first cell in this column, for the sales of 2 units of no. 7001 costing $156 each, a total price of $388 ($199 + $189) is obtained. Then I need to copy this formula down the rest of the "prices" column.
I have been racking my brain on this problem for 2 days and I am really desperate. ANy help would be greatly appreciated.
Thanks in Advance!
Jenny
Table 1. Sales Records
Product Name ID Serial Number Unit Cost Selling Price
Kodak EasyShare P850 7001 5244270 156 199
Kodak EasyShare P850 7001 3058375 156 189
Kodak EasyShare P850 7001 4786766 142 177
Kodak EasyShare P850 7001 6982965 142 177
Kodak EasyShare P850 7001 9889905 142 165
Kodak EasyShare P850 7001 4366908 142 165
Kodak EasyShare V70 5656 5646262 201 220
Kodak EasyShare V70 5656 6247505 199 234
Casio Exilim EX-Z850 9812 2846843 178 241
Casio Exilim EX-Z850 9812 3671198 178 241
Casio Exilim EX-Z850 9812 8447425 178 225
Sony DSC-N1 7753 261209 234 267
Sony DSC-N1 7753 5891987 222 258
Pentax Optio W10 2387 839551 256 305
Pentax Optio W10 2387 1281852 233 301
Pentax Optio W10 2387 2498589 233 299
Pentax Optio W10 2387 9939138 233 267
Panasonic Lumix 4444 9347263 300 388
Panasonic Lumix 4444 498636 300 355
total 4673
Table 2. Cost of Goods Sold Computation
Product Unit Units Cost of Goods
ID Cost Sold Sold Prices
Kodak EasyShare P850 7001 156
Kodak EasyShare P850 7001 142
Kodak EasyShare V70 5656 201
Kodak EasyShare V70 5656 199
Casio Exilim EX-Z850 9812 178
Sony DSC-N1 7753 234
Sony DSC-N1 7753 222
Sony DSC-N1 7753 200
Pentax Optio W10 2387 256
Pentax Optio W10 2387 233
Panasonic Lumix 4444 300
In Excel I have two columns of dates (day/month/year) for the start date and end date of an activity for a construction schedule. There are multiple activities and a timeline of months at the top of my sheet.
I would like to be able to input all the start and end dates into the two columns and have my top row automatic adjust to the earliest month to the latest month.
For example:
start______end__________jan_feb_mar_apr_may_jun__jul_aug_sept_oct_nov
jan/2010___july 2010
feb/2010___aug/2010
jun/2010___nov/2010
So when I change any of the start or end dates, my top row timeline will adjust and add month columns or decrease month columns to match my start and end dates.
Also, once I input the start and end dates, I would like the corresponding cells for that row to highlight/activate for the duration of that activity.
For example:
start______end__________jan_feb_mar_apr_may_jun__jul_aug_sept_oct_nov
jan/2010___july 2010______x__x___x___x___x___x___x
feb/2010___aug/2010_________x___x___x___x___x___x___x
jun/2010___nov/2010_________________________x___x___x___x___x___x
On the right side of those rows there is a total cost for the duration of the activity. In each highlighted/active cell I want to take the total cost for that row divided by each month for that activity and have cost that distributed into each month.
For example:
start______end__________jan_feb_mar_apr_may_jun__jul_aug_sept_oct_nov
jan/2010___july 2010_____20__20__20__20__20__20__20____________________140
feb/2010___aug/2010_________40__40__40__40__40__40__40________________280
jun/2010___nov/2010_________________________50__50__50__50__50__50____300
I realize its a complex set of formulas that tie into each other, but i am a novice excel user and there are many more experts out there that i figure could provide me insight. I appreciate your time in looking into this matter!
Please respond if you know how to any of these steps and the forumlas needed for them.
Thanks,
PMB
can anyone help me in excel
i have 2 worksheet to calculate cost of sales
sheet 1)A Barcode
B Item Name
C) Cost Price
Sheet 2) A) Barcode
B) Item Name
C) Sold Qty
D) Sold Amount
E) Cost Price
to know the cost of Sales i m using theVlookup formula but the function the formula its calculating some cells and rest its showing #N/A
=VLOOKUP(A75:A1500,'Price List'!A75:D1784,3,FALSE)
i m fed with this forumla and please give me any ideas to know the exact of cost sales
any would be appreicated
Hi.
Can anybody please help with this vlookup I'm having trouble with.
Range A1:A8 Optional Event ID Heading, Numbered 1 - 7
Range B1:B8 Optional Event Heading, Hang Gliding, etc
Range C1:C8 Optional Event Price Heading, 100,120,80, etc
What I need is the optional cost from event price when event ID is typed in, in a Vlookup formula, any help would be much appreciated.
I am trying to figure out how to update cost from a vendors .xls file I want to compare my price list to the vendor price list by OEM number and Highlight any cost that have changed and show the amount of change. I have really tried and tried but I cannot figure out how to do the formula correctly. Any help would be greatly appreciated as I have to do this manually now and we have 4000 parts from this particular vendor.
Thank You
Matthew
In my provided Excel file it will be more clear what I want...
As I purchase an Item the cost increases 10%. I need the formula that will show the combined total cost of quantity purchased.
Thanks in advance
Dan
