>>> Finding Lowest Cost/Vendor

Oct 24, 2007

I need to find the lowest price, mark it up and designate which vendor it�s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new �price� and �vendor� columns and then I can go through and mark it up.

Here�s a small sample of what the sheet looks like:

Item #Desc V1 V1 CostV2V2 CostV3V3 Cost
123456brush Advance 2.56Weiler5.65Osborn3.25

View 2 Replies

Column Title Lookup: Find The Lowest Cost

Sep 24, 2009

I have been assigned a task of finding the lowest cost of four possible solutions however I have quite an extensive list of items to work with. To make this easier, I need to be able to find the lowest cost in my row (which is not sorted by lowest to highest value) and return the column heading associated with that lowest cost.

View 2 Replies View Related

Finding The Lowest Value And Highlight With Condition?

Feb 3, 2014

question 1 : In the below sheet, is there a way to use the condition highlighting the quantity for each item which has the lowest (prices/pcs) value?

for example : comparing the ITEM 1 -> $8/10 , $6/8, $9/20, $11/20
and highlight the 8pcs for item 1, indicates item 1, seller bill has the best offer.

Question 2 : Since I will have at least 50 items, is there a way to just copy the formula and apple to each row ?

question 3 : I would also like to apply a formula that can calculate the price and quantity of each item. for example : on the I column, for Item 1, i would love to order 3 orders, since bill has the best offer, so I will have 24pcs on column I3. And i would love to apply such formula to each item for each row.

Question 4 : For the cell J3, I would like to know if bill has the best offer for the item 1, order 3 orders, the price for item 1 will be $18. And I would like to apply such formula to each row for each item.

I do not want to create another column for each seller: such as ($/pcs) to find out the best offer, since i will have at least 35suppliers , and try to make the sheet look as nest as possible.

View 1 Replies View Related

Finding The X Highest Or Lowest Numbers In A Set?

Jul 24, 2014

I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?

View 3 Replies View Related

Finding Lowest Value And Displaying Offset

Jul 20, 2012

I have a spreadsheet where I want to do some fancy stuff:

1) find the lowest value in a range of cells (S9:S17)
2) output its corresponding value in A9:A17 into another cell, say C87
3) increment its corresponding value in C9:C17 by 1
4) repeat 9 times, but inputting it to C88, then C89, and so on.

Everything in the spreadsheet depends on the values in C9:C17, including the range S9:S17, so when the values in column C change, so do the ones in column S.

But I don't want to actually change anything, just simulate it. But because the values in S9:S17 are formulas from yet another column in the spreadsheet, the best thing to do would be to remember the column C values beforehand, do the 4 steps above, and write them back afterwards, otherwise the macro starts to get complicated and brings in more values etc.

View 2 Replies View Related

Finding Lowest Percentage From Peak High To Low?

Aug 30, 2013

I had the following:

0.000%

-2.140%

-1.729%

0.973%

1.206%

1.469%

[code]...

I want to know the lowest number from the peak high to the lowest low was? If I have not explained that properly, if I had 12.00% as the highest and the next few rows consistently, and gradually moved to a low of 8.00% that would be a difference of -4.00%. So -4.00% would be my answer. formula as I need to know this one number in a column of 4000+ percentages like you see above.

View 9 Replies View Related

Finding Highest And Lowest Values In Dynamic Range?

Dec 28, 2011

I have a column that updates automatically by placing a value in in the next blank cell in the column. I am trying to write two formulas to identify the highest and lowest values in the preceding 10 cells of each update in the column but seem to be stuck in MIN/MAX/OFFSET hell.

View 8 Replies View Related

Index And Vlookup: Match The "Vendor" On The "PO" Worksheet With The "Vendor" On The "Cube File" Worksheet

Nov 20, 2009

I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.

Example:
On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?

View 2 Replies View Related

Autocomplete Vendor Name From Access Database

Apr 11, 2012

Is it possible to "Autocomplete" a Vendors name in an excel cell from an Access Database of vendors and then perform a vlookup from the database for contact name, address, phone, email, etc.... in adjacent cells?

For example if I begin typing "Acc" then I automatically get a list of vendors from my vendor database in Access beginning with Acc to choose from, such as Accent Cabinet, Access Grage Doors, etc...

View 1 Replies View Related

Use Cell Name To Create Range..when The Vendor Changes

Jan 2, 2008

I would like to create a range name when the vendor changes in column "A' and identify the range name as the name in "A".

Name Item QTY
Bill A 2
Charley C 1
Charley d 6
Charley z 7
David x 6
David f 44

I would have range name Bill for B2:C2 Charley B3:c4. Since the data changes, I need to have the program run and delete old name and create a new range with new data.

View 2 Replies View Related

Formatting Multiple Vendor Price Lists?

Apr 20, 2011

We currently have a few hundred vendors we order from for a retail business. We receive price lists in excel format, but they all differ in how they are formatted, i.e. one may have a column header of "UPC" and the next may say "UPC ID" and they may be in different columns. We currently format a handfull of these price lists so they have the same headers so we can easily compare costs, margins, etc, but we don't have the resources to do many more than this.

View 6 Replies View Related

Look Up Function Recognize The Vendor Code And Assign The Appropriate Name

Aug 8, 2006

I can only export vendor codes from the General Ledger rather than vendor names. If possible, I would like for excel to recognize the vendor code and assign the appropriate name. For example, HCC stands for Human Capital Consulting. The GL includes the vendor code and the voucher number. If I export the vendor code HCC/V0007 from the accounting system to cell A2 in excel, I would like for excel to pick up everything before the / (in this case HCC) and enter Human Capital Consulting in cell B2. I have a vendor list in excel that matches vendor code with vendor. I just need for cell B2 to pick up the vendor code from the list.

View 9 Replies View Related

Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

View 5 Replies View Related

Calculating Cost Per Second

Oct 25, 2007

I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.

Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.

To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.

View 14 Replies View Related

Calculating Cost

Apr 17, 2009

Problem - billing spreadsheet for prisoner fee.

1 - 8 hrs = $55
9 - 24 hrs = $55 + $65 or $120
Anything over 24 hrs - $65 for each additional (24 hrs) ($185)

So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.

View 5 Replies View Related

Cost Per Row Of Data

Mar 6, 2014

I have an excel table that among other fields it has a [product Charge] field.. what im trying to do is to get a cost per row based on the [Product Charge] field.. so example if I have 10,000 rows and the total for that field is 1,000,000 the cost should be 100 ( [Product Charge] / total rows).... I then want to be able to use it in a pivot table...

View 1 Replies View Related

Least Cost Routing

Aug 3, 2009

im working on a VoIP company, and having a hard time to figure out a result on a little time.

the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns:

I am including an image, sorry if the image seems to be heavy and large:

as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers.

While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate.

Red marks=are not suitable or non-profitable buy rate for the sale rate.

Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.

View 9 Replies View Related

Cost Vs Units Graph

Jan 25, 2009

I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):

Cost per unit for 0-10 units: $1
Cost per unit for 11-20 units: $3
Cost per unit for 21-30 units: $5
Cost per unit for 31-40 units: $8
Fixed cost: $100 (this is regardless of the number of units produced)

Basically I'm looking to construct a cost vs units graph.

View 11 Replies View Related

Calculating Cost Per Month?

May 8, 2014

I am trying to create vba macro that allows me to calculate cost per month.

I have created a basic table with two columns

Cost
Month

200
Jan

300
March

150
Jan

470
April

150
March

I have another sheet , that has two columns

Month - Cost
Jan
Feb
March
April

I am trying to sum up the value on sheet1 and input into sheet2 on the allocated month.

So far logic , that I have came up with is.

I create a For loop statement that looks column and sort each month into an array.

Than the second loop will use the array to find the cost with the allocated array value to sum it.

View 2 Replies View Related

Transferring Cost From One Sheet To Another

Feb 24, 2009

I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?

View 6 Replies View Related

Cost Based Mark Up

Sep 26, 2007

set up a cost based markup spreadsheet?

I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value.

I only know how to mark up values by a single percentage.

View 9 Replies View Related

Simple Job Cost Sheet

Feb 20, 2009

The job cost controller is not exactly what I need, so I have been trying to create my own. So far my progress has got me to be able to separate each laborer for each job.

Here is my query: I have a drop down menu that lists ten different tasks(i.e. carpentry, painting, demo, etc.) and I want each of the ten tasks to separate themselves. Is this a vlookup function? How do I make excel listen?

View 9 Replies View Related

Time And Cost Calculations ..

Aug 14, 2006

I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit.

The source data I have is;

1 - Start time and end time of the job

2 - Any breaks taken during the job

3 - The number of staff it took to do it

4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates

5 - The number of punnets packed.

With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning �7.50 an hour worked for 1 hour 15 mins at a cost of �0.39

View 2 Replies View Related

Cost Average Effect

Oct 19, 2006

Example number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms

Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.

How big will the difference be between example 1 and 2 ?

*** Background.
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.

Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.

View 2 Replies View Related

Calculate X Percentage Of Cost

Apr 15, 2008

I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.

View 8 Replies View Related

SUM Based On Cost Center Wise

Jan 14, 2014

I need a sum of amount based on the Cost center & Category from the details table , can i have a formula to get them .

Please find the example amount in the attached excel sheet highlighted in RED Colour.

View 3 Replies View Related

Calculating Cost Based On Several Factors

Jan 7, 2009

i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.

I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.

For example - XXX has a rate of �200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to �150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.

Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.

Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.

View 10 Replies View Related

Way To Check Whether Any Cost Centre Has Been Missed Out ...

Nov 20, 2009

I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences.

So my questions are

1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out.

2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.

View 11 Replies View Related

Calculate Cost Basis Of Inventory

Dec 14, 2008

I need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)

Please refer to attached sheet....