Multi-function Formula (=IF) (for Specific Shipping Weights)
Feb 3, 2010
I would like to write a formula for shipping cost in Excel 2003, for specific shipping weights. Each product is a different weight. The result is in a new column.
What formula would I use for this column? The figures below are hypothetical.
A1 is 4 lbs. Shipping is $6.00
A2 is 8 lbs. Shipping is $12.90
A3 is 10 lbs. Shipping is $16.00
A4 is 15 lbs. Shipping is $24.00
Formula for the column is ?
If A1 is greater than 1 lb. and less than 5 lbs. then shipping cost is $6.00 or
A1 is greater than 7 lbs. and less than 10 lbs. then shipping cost is 12.90 or
A1 is greater than 9 lbs. and less than 15 lbs.then shipping cost is $16.00
A1 is greater than 14 lbs. and less than 18 lbs. then shipping cost is $24.50
I'm exporting data from an access database into an Excel document and want to use the Sumifs formula to add up weights between a defined period (we have to show tonnages on a weekly basis).
The data and calculations are on a separate worksheets. The data looks like this:
[URL]
I'm trying to sum the data in column E based on the date range in column A and my formula is
i tried to compare 2 different cells with text inside.Each cell contains "yes","no"," ", 6 possible values
For example A B C 1 YES NO 2 YES YES 3 YES " " 4 NO YES 5 NO NO 6 NO " " In column C, i want to enter the result f.e. if a1="yes" and b1="yes" do 1,if a2="yes" and b2="no" do 2 and so on There is a function for this?
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
I am looking to sum the weight I add in my packing list spreadsheet.
below are two ways i have been working with and will settle with what ever i can get. all i need is to be able to enter the weight of a package either by lbs.oz or in separate columns (lbs)(oz) then have a formula take either way and know there is only 16 oz in a lb and give me a total weight of all things shipped.
I’m trying to add a shipping charge calculator based upon weight to a worksheet. I was trying to do with with VLOOKUP (my first attempted use of this) so I created a second worksheet called ‘Shipping’ which has the weights in column A (Weight) and the corresponding shipping cost in column B (Price). Up to 70 pounds, column A increases in two-pound increments (A2 through A36, 2 through 70 lbs); from 70 through 150 pounds, the increases are in five-pound increments (A36 through A52, 70 through 150 lbs). The total weight to be shipped is derived from SUM(D2:D30), located on the first worksheet called ‘Order’ in cell D32. In cell E32 I have entered this formula: VLOOKUP(D32,Shipping!A2:B52,2).
My problem is if Order!D32 returns something like 4.5, the value for four pounds is the result, while the six-pound charge should be the result. If I round this value up to 5.0, 5.5 or even 5.9, I get the same result.
I know I’m not supposed to guess at what I need to do, but these are my thoughts: 1) I need to modify the VLOOKUP formula to select the next higher value, i.e., so the 6 lb charge is selected for E32 when D32 contains 4.5 lbs, or the 85 lb charge is selected for E32 when D32 contains 82 lbs, 2) I need to add a ROUNDUP function to the SUM formula; this would need to round up to the next even number up to 70 lbs and then in increments of five above 70 lbs. I don’t know if either approach is correct, and in any case, I couldn’t figure out the syntax for either of these. I’ve attached a sample spreadsheet, Shipping_Charge.xls.
formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
I have a userform with 35 text boxes on which display data from a worksheet based on a selection made from a combobox.
My problem is that TextBox11 and TextBoxes 15-35 all need to show a value to 2 decimal places i.e 360.00.
I have found the following line of code:
Code:
For Each ctrl In Controls If TypeName(ctrl) = "TextBox" Then ctrl.Value = Format(ctrl.Value, ".00") Next ctrl
Which does the job but unfortunatley it applies it to every single textbox on the form (not just No's 11,15-35) which is a problem as some of the boxes contain dates so instead of 09/05/2012 i actually get 41038.00.
My question is how to I modifiy the above code (or is there an alternative code?) to only apply to the required textboxes?
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour.
Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.
I'm trying to add "yes" as per the attached spreadsheet, using forumla. Account codes are duplicated (sometimes more than once, although no shown on the example) each account code needs to have the same yes column.
I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into.
Example for A1=2,B1=4
=2*A1:B1
array enetered gives 4,8
However
=Max(2*A1:B1,5)
array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.
************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12= ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]
which is completed with CTRL+SHIFT+ENTER, and it works properly. However, I want to do the same for all lines following, where it will go to the next item matching that value. For instance: on my SALESMEN sheet, I have John Smith, Fred Johnson, and Mary Williams. Fred Johnson is considered inactive, which is represented in the SALESMEN sheet as an "X" in column H. Therefore, I want line 1 to show John Smith, and line 2 to show Mary Williams.
I have a long chain of formulas calculating the return on a particular investment in equipment. We'll call the inputs A, B, and C, and the output (total savings generated) Z. There are several set combinations of values for A, B, and C that I'm interested in generating a summary report for.
There are a number of steps in-between the inputs that I'm interested in, all of which are necessary to get to the output, but is there a good way to have excel chart inputs and outputs without filling out the intervening data?
I suppose the whole thing is essentially a multi-variable, multi-step data table.
I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.
So in cell A6 is the list.
in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.
But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this
=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
I am trying to use a count if function for a specific word and a time range or number. so On Sheet 1 (called Raw Data) I want to check for the word annuity in Column H -
If it is present I want the formula to then check if the corresponding time in column C is within a specific hour.
im trying to do is create a database of autocad drawings based on style of house number of bedrooms sq ft and if theres a garage. what i am wondering is how can i add a search function. for example i want to find a Cape with 2 bedrooms...i have all the information on the spreadsheet. what do i need to do to be able to search among the spread sheet without using the find function this is just the beginning of the data
Can Anybody help me with a function that will arange all days from January acorrdingly with the year 2010. I am attacing a sample file to make it clear. I need the function to Check which month is in Cell H1 and arrange the days accordingly to the month and the year.
In my example the code must change the Cell values Like this: ...
I need to make a list of Part Numbers in quotation marks. If a number of digits of a P/N is less than 13 a number of space characters has to be added to make the string 13 characters long.
We have example P/N: 1234567890123 12345678 123456
should become: "1234567890123(no extra space characters should be filled-13chars)" "12345678(5 extra space characters here)" "123456(7 extra space characters)"
Is there a function that inputs a cerain number of a specific character ("space" in this example)?
