Formula (retrun The Right Cost For An Item, Depending On The Delivery State)
Oct 27, 2009
I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state?
if ap10 = "CA" then look up in range AH2 to AH18
if ap10 = "GA" then look up in range AI2 to AI18
if ap10 = "ME" then look up in range AI2 to AI18
if ap10 = "PR" then look up in range AI2 to AI18
if ap10 = any other state then look up in range AG2 to AG18
cell AP10 being the cell that the state is listed in
Cell U10 being the cell that item is in (a drop down menu)
This is the vlookup formula that will get one price.
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
complicated formula for working out prices including applying discounts.
I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19
I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.
Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.
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?
run a spreadsheet for Vendor Deliveries and got as far as promised delivery date and actual delivery date with a late or on time comment. Im trying to get a percentage from this data.the excel spreadsheet that i have uploaded.Delivery Schedule.xls
I am about to apply for an entry-level job as a scheduler for a company that ships olive oil. But before I decide to actually do this I'd like to see what I'm getting myself into.
It is how Microsoft Excel is used to schedule shipments, monitor inbound shipments and keep track of inventory.
I am trying to project the delivery dates of my shipments which is sent via courier to different destinations from my Distribution Centre.
The courier company delivers the parcels across Monday to Saturday(Sunday being the off day for the courier). There are different transit days for different locations which works in deriving the project dates from the shipping date. I am looking for a formula which can satisfy all the following conditions and project the delivery dates accordingly:-
Condition1:-If the projected delivery date falls on Sunday and there is no holiday on the very next day(Monday) then the projected delivery dates needs to be on Monday.
Condition2:- If the projected delivery date falls on Sunday and the very next Monday is a Holiday then the projected delivery dates needs to be on Tuesday.
Condition3:- If the projected delivery date is a Holiday and the holiday falls on any day between Monday to Friday then the delivery date will be the next working day post the holiday(between Mon-Sat).
Condition4:-If the projected delivery date is a Holiday and the holiday falls on Saturday then the delivery date will be next Monday.
Condition5A/5B/5C:-In case there are more than one holidays at a stretch(2/3 days of holidays at stretch) and if the projected delivery dates falls within the holidays then the projected delivery dates needs to be on the next working day(Between Monday to Saturday).
I have tried to write a formula but it is only satisfying the Condition1,Condition2 & Condition3. I am providing a sample data with desired results based on the above conditions. Any single formula which can derive the desired results.
Sheet1 ABCDEFGH1Destination LocationDispatch DateTransit DaysProjected Delivery Dates(Desired Result)Remarks Courier Company Holiday List2A29-Mar32-Apr Condition1:-Projected Delivery Date As Per Transit-Days Is Sunday(1st Apr) & There Is No Holiday On Next Monday(2nd Apr) & Hence REVISED Projected Delivery Date Needs To Be On 2nd Apr(Mon).
Workbook has following sheets: "equipment List" and "vlookup data"
"equipment list" has following fields where cost is the field that needs the vlookup data value.
model, building, cost
"vlookup data" has following fields in 2 different ranges. range1 manfacturer, model, cost-usa, cost-canada,cost-england range2 building, country
requirement is that "cost" field in "equipment list should have the value based on the model and cost with respect to the building value.
i can do simple vlookup to get "a cost-country" value but fail to understand how to ensure the value comes from the correct cost-country column based on the building info.
I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.
Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.
I am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
VB: 'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected) Private Sub ListBox1_Click() ListBox2.ListIndex = ListBox1.ListIndex End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
When copying the formula =SUMIFS(G3:G103,I3:I103,"3") how do I modify it so that only the "3" rolls to the next integer and the rest of the formula remains constant? In other words the 3 would roll to a 4 when copied down a column and the rest of the formula does not change?
I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
i have a sheet which is being used as a payment advise with subsidies. depending on the product selected, it is either a $value multiplied by the qty or a % of the total value.
what i am trying to do as an example:
if cell F46 = percent(or sheet2 b6) it needs to multiply by cell H32
BUT if cell F46 = $ (or sheet2 B7:B11) it needs to multiply by cell B46
(sheet 2 has named ranges to be used in a list box)
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.
I can get a grand total by summing the unique items by using this formula:
and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."
However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
i need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then Range("C2:C9").Sheets("Sheet1") = "x" End If
I am getting close to finishing the drop down menu capability when filling in column L in tab Transactions. However, there is a snag. When I enter part of account say "fin" (the important part here is that the part of the word should not be the beginning of the account name) - then I select an account from the menu - but it does not stay in the cell if the part of the name is the beginning of the account name - all is fine.
I have created a drop down list for items we quote on. each item has a different formula depending on the variables on sheet one Sheet1. I have listed the formulas in the column next to the drop down list for each item, but our now need the main quote to put in the formula in another cell when the item is chosen from the drop down list.
My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
