If And VLookup - Calculate Charges Of Freight Based On Predefined Parameters
Dec 13, 2013
I want to create an Excel sheet that calculates the charges of a freight based on predefined parameters.
I got 3 service types as shown below, two freight types and 12 different destinations both NT and SA have 6 different destinations:
I want Excel to do the following:
If Road and Carton and to NT-Country and weight is 7 then 7*1.000089 is the result
1 to 15
>15
Service
Type
NT
METRO
0.337284
0.337284
[Code] .......
View 7 Replies
ADVERTISEMENT
Jan 21, 2009
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
View 9 Replies
View Related
Feb 25, 2009
what i trying to learn is that im trying to get the delivery price calculate base on the delivery area(F10) and the total qty of the items(G10).
but i've tried with Vlookup, IF, lookup, Hlookup function and i still cant manage to get the right one to put the data in the H10
View 4 Replies
View Related
Jun 20, 2014
Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rated.
For example:
Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318
So if my usage was 1200 KWH...
((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86
I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?
View 9 Replies
View Related
Dec 8, 2008
but, after subtracting those days when they fell between Holidays / vacation periods.
See question, in details, inside the attached workbook.
View 10 Replies
View Related
Oct 22, 2008
I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
View 3 Replies
View Related
Apr 1, 2014
Excel Question 1.jpg
I need to have a code that looks up Column 1 (Parameter 1) first, then looks up Column 2 (Parameter 2), and inputs Column 3 (Value). The code needs to be in a SINGLE cel.
I thought initially using a Lookup function within another Lookup function might work, but I can't seem to get the code to work properly.
View 7 Replies
View Related
Dec 7, 2013
I'm working on an attendance tracker for church. We will have folks scan a QR code and fill out a 2 question survey: Name of person being checked in and date of check in. Both are mandatory and are set in such a way as to always have the same formatting every time. That's my first sheet, it just gathers the data. My second sheet actually has the attendance tracker with the roll and dated columns. What I'm trying to create on the second sheet is a formula that will search the first sheet for the date and name and return a 1 if they were there that day.
View 7 Replies
View Related
Nov 16, 2009
I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself.
Here's my conundrum:
A potential customer enters "80802" for zip code and "Solomon" for store.
StoreLocation_________ City_______ State_____ Zip______ Distance
Solomon__________ Arapahoe _______CO ____ 80802_____ 270
Garden City_______ Arapahoe_______ CO _____80802_____ 143
The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.
View 14 Replies
View Related
Mar 2, 2007
I have coins in the following denominations: 10 cents and 20 cents
IF my parking charge is 30 cents, I can pay the charge using:
a: 10 + 10 + 10 cents
b: 10 + 20 cents
c: 20 + 10 cents
= 3 combinations (a, b, c)
= 2 unique (a, b) or (a, c)
If my parking charge is 80 cents
What is the formula to use to find:
(i) The combinations (answer should be 34(?) posible combinations)
(ii) The unique combinations
whilst if the parking charge was 50 cents
Than there would be 8 possible combinations (as the answer to the formula!)
View 9 Replies
View Related
Dec 19, 2013
see attachment.
A2 is the given length and B2 is the given weight. I want B14 to net the answer of "Type G" because the length (A2) is equal to C10:C12 but the weight (B2) is within D11 and E11.
How do I formulate B14 to net the answer of "Type G"? This has to be flexible as the data in A2 and B2 will be entered in differently each time and B14 needs to find the appropriate "Type".
View 3 Replies
View Related
Mar 1, 2013
General accrual spreadsheets. I'm looking for an accrual spreadsheet where I can estimate freight and also for our gas, electric and water usage which is tracking usage down to the day.
View 2 Replies
View Related
Feb 11, 2013
How to autopopulate numbers from data sheet based on Selected month.
View 4 Replies
View Related
Jul 5, 2013
1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
View 2 Replies
View Related
Mar 11, 2008
I get about 10 text files at the beginning of each month that I need to import into Excel. The file layout does change slightly from month to month so I have an Excel spreadsheet with the layout as the first worksheet. Each text file is in fixed layout form rather than delimited. The structure of that page is that the name of the first text file is in cell A1. The cumulative field widths are below that and the data formats are next to the field widths (in the adjacent column “B”). T=Text, G=General, D= Date with 3 flavors of Date: YMD, DMY and MDY. For the second text file, it repeats the structure in columns “C” and “D”, the third text file in columns “E” and “F” etc. What I would like is for the macro to open the first text file (based on the name in cell A1) and import it into a new worksheet (same file) using the cumulative field widths in A2 through A8 and the formats from B2 through B8, then import the next text file to a new worksheet (same file) using the cumulative field widths in C2 through C8 and the formats from D2 through D8 and keep going until all the files have been imported. The sheet names should be the same as the file that is imported (the “.txt” part of the file name isn’t important). I’ve attached a copy of the Excel spreadsheet that has the file names, field widths (cumulative) and field formats and a 2 row sample of the text file for the first import.
View 6 Replies
View Related
Dec 12, 2011
I am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.
The Access table will have three columns:
Part Number|Line|Catalog Code
I would like to import all three columns, but allow the user to select the records imported based on the catalog code.
The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.
I have 2007 versions.
View 1 Replies
View Related
Mar 4, 2014
I need a courier rate returned based on the zone and weight of the shipment. The rate table has 10 zones (B1:K1) and 12 weight classes (A2:A13)
In the sheet "data & result" I want the cost returned in the yellow cells from looking up the weight (D) and zone (C).
View 2 Replies
View Related
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Jul 9, 2009
I am trying to create a multiple v look up formula to calculate the mileage from two destinations. I have a table with a From column, To Column, and miles column with data.
I would like to automatically calculate the mileage determined upon what the user picks as the two destinations from a drop down list. I thought it would work like this =VLOOKUP(B5:C5,G3:I14,2) But I'm not sure if I can have a range as the lookup value. I have attached my sheet I am working from.
View 3 Replies
View Related
Dec 19, 2013
I would like to use vlookup to calculate the number of shift per week for each agent
View 10 Replies
View Related
Jan 31, 2013
I am trying to create and calculate player statistics using Vlookup in Excel. This is what I am trying to do:
BCD
5PlayerGoalsAssists
6Jordan Eberle00=VLOOKUP(TEXT(B6,"@"),B13:G14,4) used in C6
7James Neal 00=VLOOKUP(B7,B13:G14,4) used in C7
cell format is "general" in B6 thru B14
Lookup TableCol ECol FCol G
Player NameTeamGPGAPts
13James NealPITT4325
14Jordan EberleEDM1404
Imported Data from Yahoo Sports and have sorted in ascending order a-b-c-d
(pasted as text and than changed to General in Excel formatting)
I have tried using 2 different vlookup formulas structures, but no success. I have tried using numeric reference (i.e using numeric 1 in for both in the lookup table and the original cell) and that works.
View 3 Replies
View Related
Oct 12, 2009
Struggling a bit on this one! I have tried the index function, but doesn’t quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is ‘Index’ is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
View 5 Replies
View Related
Feb 13, 2014
I am having a set of names in one column and would like to print various combination of names in to another column. In sample data set, there are 30 names of entries in column B and like to print 4 data set i.e. 1st three set of combination of column B print into the column C similarly for other set of six, twelve and all kind of combination. Please find the detail in attached excel sheet.
View 3 Replies
View Related
Jun 25, 2014
I've seen a lot of traffic around running macro's on a clock at regular intervals. But what if I want to run a Macro (Macro1) at 8:55, 9:55, 11:55, 12:55, 13:55, 16:55 and another Macro (Macro2) at the top of each of those hours? The string of times is not consecutive, so I have built a table to define the release times. At each of these times I need to run a macros, and I'm not sure where to begin.
View 3 Replies
View Related
Dec 24, 2008
I have a button in my worksheet. When clicked on I want to change the text in this button
so far so good. I have however a problem how to return to the cell that was active, the moment I clicked the button. so far I have the following code
View 3 Replies
View Related
Apr 9, 2009
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.
I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.
The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".
View 2 Replies
View Related
Mar 24, 2007
After all the work and calculations applied to data from various macros, I would like to have a final macro simply insert a title row, always the top row with various titles. Theres 11 columns, thus it would be great for the code to simply insert the row with predefined bolded titles, such as T1, T2, T3....T11. These 11 cells would than share then same bolded bottom line, making it visibly separated from the rest of the rows. I know this is easy, and Ive looked throughout the forum, but I can find things close, but not close enough.
View 2 Replies
View Related
Jun 23, 2009
I want B1 to copy from A1, but if A1 contains a certain text string, then remove that text string and keep everything else. For example, I want B1 to remove "ru" "la" "fm" and "mu" so if A1 is 5464ru-xl then B1 will return 5464-xl or if A1 is 36944la-s then B1 returns 36994-s
View 14 Replies
View Related
Dec 30, 2009
My problem is that I need to get button to move information from sheet1 A5:E5 & A8:E8 & A11:E11 to sheet2 B1:P1 if Sheet1 A1=1 to B2:P2 if A1=2 etc
I can only make it by if or case macros, but it becomes so long code
View 2 Replies
View Related
Feb 8, 2012
I'm looking for a cell formula. here is an example:
In cell A2, I want it to populate automatically with a predefined value if cell A1 is a certain colour, as below.
A1 A2
559.00
I have conditional formatting in cell A1.
View 6 Replies
View Related
