Multiple IF's And AND Inclusive Formula (all In One Cell) That Would Look At The Above Table And Depending Upon The Price Paid
.............................24............30............36
300014999..........9.00%.......11.00%.....12.00%
1500099999........9.50%.......11.50%.....12.50%
100000249999.....9.00%......11.00%.....13.00%
I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
Lookups For An Item's Price From A Price List
The analysis basically has 2 data components to it: The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time. The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased. What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below: 1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2) 2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least) 3) Pull the price for this "closest quantity" I have uploaded a worksheet showing the structure of that data. [url] Is there some VB code I need to do this, or can it just be a few simple formulas?
View Replies!
View Related
'shift Cells Right' Depending On Value Of Cell
I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell: Selection.Insert Shift:=xlToRight Range("B2085").Select ActiveCell.FormulaR1C1 = "Click for detail image" With ActiveCell.Characters(Start:=1, Length:=22).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic Note that "B2085" above is the cell number that the script stops on to execute this code.
View Replies!
View Related
Sum Up The Totals Of 'quantity X Unit Price' For Each Product
i am currently working on cost analysis of large projects. I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product. I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.
View Replies!
View Related
Creating A Mat Table Based On A Price Table
Part 1: Pricelist The user presses 1 of 3 macro buttons to access the price list. The buttons represent 1 of 3 parts of a construction project. The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro, depending on which button is pressed, hides all but the relevent quantity col & total col. Each job phase needs its own total & having it setup this way makes it easier to enter new items into the table (only have to enter the item once, rather than 3 times). The first html shows a simplied example of the "price list" described above (before & after the quantitiy & price cols are hidden). The user adjust the quantity & the sheet calculates the price total. Part 1: Material listThe material list is a list of items & quantities the user selected in the quantity col of Price list. This list is to be printed & given to the workers. It does not show price, only item & quantity. This list, unlike pricelist, must be visiablly broken down by job phase. I dont want the material list to be the price list repeated vertically on a sheet 3 times. This would take up too much space & require new items to be entered once in the pricelist & then 3 times in the mat list. I would rather the mat list be on a seperate sheet from price list & have a formula in cell matlistA1 that searches the phase 1 quantity col (then phase2 & 3) for the first #>0. When a #>0 is found, the formula would return an item name into the cell on matlist. Then the formula would run in matlistA2, & search for the next #>0. (& so on until a complete mat list table, broken up into phases is created) Sheet2 ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73 ..............................
View Replies!
View Related
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
Formula For How Much Interest Would Be Paid In A Given Timeframe
I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period. I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row. For example Balance | Rate | Payment | Time Period | Interest Paid $1,000 | 10.00%|$20.00 | 6 | $48.56
View Replies!
View Related
Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets
I have been running a simulation for about 18 hours now and just received: Run-time error '1004': Method 'Add' of object ' Sheets' failed I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using: ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count) The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
View Replies!
View Related
Multiple 'if' Scenarios In A Formula
What I am trying to do is a formula for two different 'if' scenarios that will return one value for one answer and another value for the other. They are not numbers, they are text. For example: if the cell contains 'pcs' value would = no if the cell contains a dollar amt value would = yes
View Replies!
View Related
Count Cells Depending Upon Multiple Cell Values
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83, Order No OP Posted Set Allowed Set 30761157 00100.731.500 3076115700100.500.000 3076115700100.600.000 3076461000100.000.000 3076461000101.050.500 3076524400100.000.000 3076524400100.550.500 3076639600100.000.000 3076639600101.180.500 3076810900100.000.000 3076862900100.000.000 3076862900100.000.000 3076862900100.000.000
View Replies!
View Related
Add 5% To Many Price List's Macro
I have around 150 separate price list files and i would like to create a macro to findformat currency then copy 1.05 and use Edit | Paste Special (value, multiply)...... then round up or down to nearest cents .567 would roundup to .57 The findformat and Paste Special will work when i manually do it.... but when i record as macro it will not work when played back...... Here is the code it records... code does not include the roundup part. I dont know how to do that. Range("M3").Select ActiveCell.FormulaR1C1 = "1.05" Range("M3").Select Selection.Copy Application.FindFormat.NumberFormat = "$#,##0.00" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Range("M3").Select Application.CutCopyMode = False Selection.ClearContents End Sub
View Replies!
View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters. Samples of what I've tried: =IF(LEFT(L23,1)=P) =IF(MID(L23,1,1)=P) etc. I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
View Replies!
View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E. What I am looking for 1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that. 2) I would like this validation/formula to be applied to all the cells in a particular column. 3) Nice-to-have. If the user enters a lower case letter, then I would like to convert to upper case.
View Replies!
View Related
Days Between 2 Dates Inclusive
I have a simple formula to work out the number of dates between to dates ie: =U6-T6 This works out the days between 9 may and the 31may as 22 days but i want to show the correct number of days including the 9may so this would be 23 days. I tried this =U6-T6+1 which was fine but all the empty cells now show the value 1 in them.
View Replies!
View Related
Multiple Pictures In A Price List
Hi, I've got a price list with 2500 different lines which the boss has decided need a picture against each one (the guys a legend!!). Can anyone help me with a macro that will look up the code in Column A, then add .jpg on the end, and insert a picture into Column J?
View Replies!
View Related
Lookup Values From Multiple Formula Table
I have a sheet, called "output", in which I need to complete column C "calculated values". I need to complete the table based upon a formula table (which is in sheet "formulas"). For the first row of data, cell C2, I need to take the price per order ($0.25; cell A2) and number of orders (40; cell B2) and copy data to cell B4 and B6, respectively. Once the data has been copied to to cells B4 and B6 on the fomulas sheet, I need to copy the calculated value in Row N to the output sheet. Note that the value being copied from N can be N11, N12, N13, N14, or N15 (the one that is <> to null).
View Replies!
View Related
XL2003 Replacing Multiple Codes Within Formula's
I have a column of 3 letter codes (Righthand side) all differant ie BHP, CBA, NAB, WBC etc. (2000 of them) On the left I have the formula ie =MisData!VOLUME!BHP.ASX Each formula should have the relevant code within it such as the BHP in the formula on the 2nd row should be CBA, the third formula should have NAB in it. right down the whole 2000 codes without doing it one by one. Now using the find & replace tool to do it one ny one =MisData!VOLUME!BHP.ASX BHP =MisData!VOLUME!BHP.ASX CBA =MisData!VOLUME!BHP.ASX NAB =MisData!VOLUME!BHP.ASX WBC
View Replies!
View Related
Multiple Product Order Form With Price Lookup
On an order form the customer will be able to enter the width and projection of several products that they want to order. The way these products are priced are in a Matrix of Width over projection. The current system i have designed is: 1. The pricing Matrix's have all been put into one big table and given a unique id per product matrix. 2. An advanced filter has then been run and and it extracts the appropriate matrix and copies it onto the process sheet. 3. An Index/Match formula is used to find the price for the inputted width and projection. It does this by finding the intersect point of the width and projection on the table. Currently this will be put into a macro and assigned to a button. The problem is that up to 15 (or possibly more) products need to be able to be ordered in one order form. With the current system it means there will be a lot of Advanced Filters and there will be a macro button that will need to be clicked after every product order (and they're could be 15 or more). Obviously this isn't very professional, it is time consuming and must be the hard way of doing it. I was wondering what over systems that could be used for this sort of thing. The more solutions there are to this problem the better. Attached is the file. If you do post a solution it would be preferred if you could also post a file with a working example in. I find it much easier to learn the solution if i can see it working.
View Replies!
View Related
Cells' Color Change Depending On Dates
I have column "C", starting from "C5" with dates till C200, but not all cells have dates some of them are also blank. I want the cell to change color depending on the date. If the written due date is in a month from today, I want it to turn yellow, and if the due date is in the past from today, I wanted to turn red. I want it to get updated every month.
View Replies!
View Related
Populate Cells/Table Depending On INDEX Result
I am trying to populate a table (a7:c27) in the attached sheet with data. First, I must enter a number 1-16, which uses index function to return either "series" or "base" If it is base, i want the number in the corresponding table filtered down - i.e. if 1 originally picked then h7 is (40) is entered into each month as base = constant. If the result is series, then the data in i7:z7 is entered into the the 18 month table.
View Replies!
View Related
VLOOKUP Formula For Price Sheet
The task: create price sheet that calculates pricing based on 2 criteria - quantity, and production time. There are two worksheets. #1 is the main calculator, #2 is the price sheet, broken down with time (in hours) on the left column B, the quantity across the top in row 1. There are then boxes with different prices based on both the hours and quantity of products. On worksheet 1, I have specified quantity in C6, and time in C8. How do I pull a price in to F8 that calculates based off the time and quantity filled out on sheet 1?
View Replies!
View Related
Pivot Table: Adding A Percentage Field As '% Of Subtotal'
In the attached Excel file, there is a pivot table. In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total". The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).
View Replies!
View Related
Extracting Source Data From A 'text-only' Pivot Table
I work in my Client's office and assist in settling construction disputes. Part of this work is to browse/search their server for documents that may assist in strengthening their case. During this review I have found an excel document which is a text-only version of a pivot table, ie someone has done a copy, paste special, values into this sheet. I need to extract the original source data from this table back into the list format, as the original source of the data cannot be located The row titles on the left are activity descriptions, the column headers are dates and the data in the body of the table is hours. As an idea of size the data is spread over 213 columns and 45 rows. Their are more blank cells in the table than entries. What I would like to do is create the data in it's original form ie Column A; Date Column B; Activity Description Column C; Hours and have a separate row for each instance of an entry of hours from the pivot table.
View Replies!
View Related
Delete The Formula In The Amount The Unite Price Comes #value
(To keep things simple from left to right Column A-H) The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed. I have a vlookup formula in Column F (thank you VoG) =IF(ISNA(VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)),"",VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)) To pull prices from another worksheet.
View Replies!
View Related
Manually Enter % And Update Price (Formula)
I'd like to be able to add 10% to column D and have the prices in A, B, C change accordingly. Is there a formula for this? A B C D 2009 Distributor CASE Price2010 Distributor CASE Price2010 Distributor EACH PriceVariance from 2009 price135.00148.000.0592108.25100.000.040062.8875.006.250066.6096.008.0000 PS: Right now I have it set up working the opposite way, the prices are entered and my formula tells the user by what % the price has changed. The user wants to be able to tell the formula what % is desired and have the prices change accordingly. The formula I am using for the way column currently calculates is: =IF((G2=0),"",(((G2-F2)/F2)*1))
View Replies!
View Related
Formula To Calculate Price Based On Sheet2
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged. Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution. Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View Replies!
View Related
IF Formula Used To Price Life Insurance Policies
I price life insurance policies and need a formalu for the following new Fund: If the sum of the first two years of premium added to 10% of that total is equal to or less than 25% of the face amount, the case fits into the general parameter. Here is what I have, but it isn't working: =IF((U2+V2)*10%)+(U2+V2))
View Replies!
View Related
Building A Formula To Increase Price List 8%
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View Replies!
View Related
Small Business Formula For Price List
I need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins. Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time.
View Replies!
View Related
Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current) Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines Sheet2 (has current material codes plus current pricing), has about 36000 lines I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
View Replies!
View Related
Looking For The Closest Price To A Reference Price
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price? I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances. I would also like to reference the source on the next column.
View Replies!
View Related
Pivot Table 'views'
I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot.
View Replies!
View Related
Assign Multiple Cell's Values To A Variable
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
View Replies!
View Related
Multiple Sums Depending On A Description
I really don't know how easy or difficult it might be, but here it is: I have something like this (example) : -------------------------------------- | A | B | C | D | -------------------------------------- 1 | 15| W1 | | | 2 | 5| W2 | | | 3 | 7| W1 | | | 4 | 9| W3 | | | 5 | 21| W4 | | | 6 | 14| W2 | | | -------------------------------------- I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)
View Replies!
View Related
If Statement: Return 'V,' If Cell='&'
When I tried using if & or statements I got an error - so I tried this: =IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","") I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?
View Replies!
View Related
Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me Run-time error '1004' Method 'Range' of object'_Global' failed From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....
View Replies!
View Related
Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed
I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed. The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.
View Replies!
View Related
|
TRACKER
