Discount Formulas: Cells To Calculate $40 X (100%-10%) = $36
I would like to ask the formulas for this... for example:
$35 .. discount 10% + 25%
instead of having so many cells to calculate $40 x (100%-10%) = $36
$36 x (100% - 25%) = $27
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message. give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount? attached is the start of the assesment question. You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid. Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received: • If students pay the course fee less than 7 days prior to the course commencing then they receive no discount. • If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%. • If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%. • If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
View Replies!
View Related
Conditional Statements Using IF: Get A Discount (no Discount) Not To Apply The 10%
I have attached a 2004 excel document that has a conditional statement that says, if an amount is over $400.00 you get a 10% discount, else, the cell says no discount. That is working fine. I also have a absolute cell ready to do the math for the 10% amount. There is also a column that shows the discount amount and column that shows the amount after the discount has been applied. Since I have been just doing one calculation and auto-filling, my problem is, how do I get the record that doesn't get a discount (no discount) not to apply the 10%?
View Replies!
View Related
Formulas Display And Do Not Calculate
I am a little lost on this, and have tried to remedy my issue by searches to no avail. I have a cell formula that references another cell: Cell A1 (=A3) where Cell A3 = 1 Cell A1 displays the text "=A3" and not the value of 1. Autocalc is on, and the worsheet and workbook is unprotected.
View Replies!
View Related
Formulas To Calculate Between Two Worksheets
To start, have 1 sheet with approx 25 columns of data and i need one column to check 3 others + another sheet to complete its fields. In column M i need to enter some prices from another worksheet (tag name 'prices') but these prices depend on whats completed in the fields in columns L, T & V. Column L has fields completed with eg. Houses, Offices, Warehouses Column T has some fields completed with eg. B1, D, E (these are just zonal refs) Column V has some fields completed with numbers 1,2,3 Prices worksheet has 4 columns A = Property (same heading and fields completed as Column L on first worksheet) B = Standard Price (this has prices in fields that correspond to the properties in A above) C = Cresta Price (same description as above) D = Wind Price (same description as above) I need column M (blank fields) to look at L (heading 'property') then add in Standard Price from B (worksheet prices) then check to see if any fields are completed in Columns T & V and if they are then it needs to addon the prices from either columns C or D from Prices worksheet.
View Replies!
View Related
Calculate Formulas Based On Top 20%
I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order. At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80. EXAMPLE: TOP 20% £1,330.00 £1,070.36 £997.50 £840.00 Employees - 4 SUM - £4,237.86 Median - £1,033.93............................
View Replies!
View Related
COUNTIF Formulas To Calculate Different Conditions
I am using =COUNTIF formulas to calculate different conditions. such as, calculating how much of one product do we have and how much is is received under 30 days. i want to combine those formulas in one cell to calculate two conditions in different columns. 1-So first i found out the date difference. I used =DATEDIF(Sheet1!C10,Sheet1!E10,"d") 2-Then I found out how many of one type do we carry, using the =COUNTIF. =COUNTIF(Sheet1!J2:J3810,"DAZ") 3-That was easy, But now i have to sepereate them into 30 or less, 60 days or less, 90 days or less. Then i used =COUNTIF(D2:D3810,"
View Replies!
View Related
Formulas: Calculate Without Manual Calclation
I am thinking that I must have something set up wrong some place, but have not done anything that should have changed my formulas Its not that they are gone but just are not auto calculating. I tried just a basic sample invoice and the figures do not calculate without manual calclation
View Replies!
View Related
Chart Not Updating When Source Formulas Calculate
I have the same question as described in Graphs Not Updating When Source Data Updated; however, the poster did not reply to the request-for-clarification questions. My chart does not always update when my source data changes. My Calculations setting is Automatic (not Manual). There is no macro code working to produce the source data or the chart. My chart is on the same worksheet as my source data. The source data is simple links to other cells in the same workbook that update as the underlying data is updated. Saving doesn't work. Manually calculating (F9) doesn't work. The only way I have found to get the correct data to display in the charts is to open the Source Data and re-enter the Series range. Obviously, that gets old.
View Replies!
View Related
Efficient Lookup Formulas: Calculate The Time
Im having an awful time with the time it takes to calculate my formulas. I have probably written some of them ineffiecent. =INDEX( Website_Query!$A$2:$Z$10000,MATCH(1,(Website_Query!$A$2:$A$9725=B1144)*(Website_Query!$C$2:$C$9725="EA "),0),4) =Q1144*2 =IF(ISERROR(VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE)),"",VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE)) =IF(ISERROR(Q1144+AR1144),"",Q1144+AR1144) =IF(AK1086="q2",AL1086+AS1086,IF(AM1086="q2",AN1086+AS1086,IF(AO1086="q2",AP1086+AS1086,IF(AQ1086="q2",AR1086+AS1086,"")))) ="q"&INDEX(Website_Query!$A$2:$L$7725,MATCH(1,(Website_Query!$A$2:$A$7725=$B1086)*(Website_Query!$C$2:$C$7725=$C1086),0),5)
View Replies!
View Related
Array & Lookup Formulas Slow To Calculate
I am looking for ideas on how to speed-up one workbook. I have a spreadsheet with hundreds of lookup formulas and array formulas which are very slow to recalculate (takes around 5 min). Good thing about it is that these formulas are located in a specific range and that I need to run them only once a day (in the morning). Since I need to refresh the rest of the spreadsheet frequently, I am looking for an idea on how to isolate this resource heavy areas. One solution I've been thinking about is moving these complex and slow formulas to VBA and run them only when needed. Can anybody assist me with the best and easiest way of doing this. Ideally I would want to leave option to the user to later edit this formula. Does anybody have any experience with weather it is best to migrate complete formulas to VBA or maybe leave formulas in the spreadsheet and run them based on predefined flag (that I can switch on or off from the code)?
View Replies!
View Related
Discount A Row
Is there a way to insert a discounted row at the top of an existing spreadsheet? I'd like to name it "0" or "Row Zero" or "MasterRow" or something similar, such that the existing rows retain their original numbering, and none of the existing code is effected.
View Replies!
View Related
Discount Function In Macro
I have a quote system which takes data from tabbed sheets if there value is greater than 0, then does a simply formula which is then times by a discount which found by searching for the sheet name and then attaching the appropirate discount. In short this means that the whole forumale doesn't work and is not displayed
View Replies!
View Related
Discount Based On Amount?
I need to create a price list a we give a discount based on the price of the product for example:.... All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
View Replies!
View Related
Cost Price Vs Quantity Discount
I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View Replies!
View Related
Calculating A Discount Based On Certain Rules
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100. We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on I use Excel ver 2003. Trust my query is reasonably clear?
View Replies!
View Related
Track The Levels: Club Is 0 To 19 Points And Yields An 11% Discount
I have a 4-level program. The levels are Club, Bronze, Silver and Gold. Each is awarded when a certain amount of points are gathered. Club is 0 to 19 points and yields an 11% discount. Bronze is 20 to 44 points and yields an 12% discount. Silver is 45 to 59 points and yields an 11% discount. Gold is 60 and better and yields an 11% discount. I am looking for a way to total the points and have the spreadsheet automatically calculate the leve and percentage. So if my total points is 22, I would need the formula to return "Bronze" in one cell, then "11%" in the next.
View Replies!
View Related
Figuring List Price - Cost To Show Discount Percentage
List Price $46.98 (e2) Net Cost $19.53 (e3) How do I enter a calculation that will show me my discount percentage from my supplier? (e4) I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View Replies!
View Related
Cells Formulas
the cells"A1" value = DISLCM864244984921MW#Q195FR " how can splited the cell value just "864244984921" = "B1" Have a huge data needs to count the 12 digi of numbers.
View Replies!
View Related
Keep A Formula At 100% Or Less
i need a way to keep a formula at 100% or less, i get a number higher then 101% that is not my way i want it. i get like 120%...600% and so on, when i add a formula up i just need to keep at a full 100% even tho its more then 100%, if its less that fine like 79% ect, just dont want it to go over, if there's a ways you guys can find it out XD i just know it.
View Replies!
View Related
Compare Formulas In Cells
i wonder if there is a way to compare the formulas in two cells and not their result. I use a worksheet as a model and i want to track unwanted changes in the formulas between any new worksheets created and my original worksheet.
View Replies!
View Related
Merge Formulas Of 2 Cells Into One
I would like to merge the following formulas into a single cell . . . =IF(ISNUMBER(MATCH(D61,{"E"},0)),IF(D66>=VLOOKUP(D61,{"E",3.1},2,0),"NM","X"),"") =IF(ISNUMBER(MATCH(D61,{"S"},0)),IF(D66>=VLOOKUP(D61,{"S",30.1},2,0),"NM","X"),"") In the new formula, I need to add the following: If D61="E", then I need the cell shaded in red with black font If D61="S", then I need the cell shaded in bright yellow with black font
View Replies!
View Related
Locking Cells That Contain Formulas
I currently have my template protected, and I've been getting complaints that they can't modify the width, fonts etc. The only reason I have it protected is because I don't want them to mess with my formulas. How can i protect ONLY the formulas, and have the rest editable.
View Replies!
View Related
Formatting Cells In Formulas
I am trying to adjust a simple formula that I am using: =IF(D3<>"",1,"") **Note this is clunky, but ISBLANK doesn't seem to work due to D3 containing a formula** to also change the fill color of A3 to "Red" if data exists in D3. Everywhere I look online for the formula reference tries to point me to Conditional Formatting, Which doesnt seem to apply to seperate cells so I can't use this.
View Replies!
View Related
Recompute The Formulas In The Cells
How do you force excel to recompute for the formulas in the cells. I know excel automatically does it however running the delay in recomputing the formulas is making wrong reference. I need to monitor the location of the cell using the match function.
View Replies!
View Related
Moving Cells With Formulas
I am trying to move a group of cell containing formulas from one sheet to another, I have tried copy & paste/ ctrl & alt / paste special but for some reason the initial cell references of the formulas are not updated.
View Replies!
View Related
Discount Prices Based On Order Quantity And Package Quantity
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate: Sheet 1 ABCDEFG 1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3 210010.91000.744000.6 Sheet 2 ABCDEFG 1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3 210010.91000.744000.6...........................
View Replies!
View Related
Sum The Last 6 Numbers Below 100
I've been trying to come up with a formula for this for a few hours now, but I can't work it out. I'm using Excel 2002. In column C I have a list of 50 numbers. They range from cell C1 to C50. The numbers are either above of below 100. Now, in cell D51 I want to sum the last 6 numbers in the list below 100. So if the list would be: ... C40 = 19 C41 = 25 C42 = 3 C43 =234 C44 =0 C45 =12 C46 =345 C47 = 0 C48 = 129 C49 = 1 C50 = 12 Now, the last six numbers below 100 (going upwards) are: 12, 1, 0, 12, 0, 3. These numbers need to be summed. The outcome in this case would be 28.
View Replies!
View Related
One Hundred Back To 100
I found a way (on this board) to spell out numbers...I.E.: 140 = "One Hundred Forty" . But is there a way to convert "One Hundred Forty" to 140 and format as a number or general or pretty much anything but text? I thought would be as easy as Cell Format > Number
View Replies!
View Related
Autofilter Is Not 100% Unique
When I use the advanced filter like this: Dim oRange As Range Set oRange = ActiveSheet.Range("A:A") oRange.AdvancedFilter xlFilterCopy, , Worksheets("Sheet2").Range("B1"), True Under certain conditions, I get one duplicated value on "Sheet2". As near as I can tell so far it is only when the first cell in my "oRange" variable has a duplicate(s) of itself elsewhere in the list. The said value then appears twice in the list (only twice out of 19 instances in my particular test). Also, I noticed that it is automatically naming the first cell in my destination range "Extract". If I change "B1" (above) to something else and run it again it throws an error because of the duplicated name "Extract". What am I missing? What is the purpose of the automatic naming? It looks like I need more to guarantee that my filtered list is always 100% unique.
View Replies!
View Related
Numbers Add Up To 100%
figures as whole percentages and that is getting me hot under the collar - as my little lists rarely add up to an exact 100%. Now, I can understand where the discrepancy is creeping in with the rounding and all - but is there a sneaky way to get Excel to make the adjustment for me so that the percentages always total 100%? Here is an example from my sheet, where the actual figures are added to equal 200, and the cell showing the percentage figure has a simple formula at present of for example "=(B71/B76)" where B71 is the actual figure (in this case, 12) and B76 is the sum of all the actual figures (200 below) and the cell is then set to be a percentage with zero decimal places. 12 6% 105 53% 44 22% 31 16% 8 4% 200 So you can see that my percentages here total 101%! I have spent hours trying to research a sneaky way and am starting to think I'm asking for the impossible - and will just have to 'adjust' the percentages manually to make my 90 charts look accurate!
View Replies!
View Related
Macro That Insert Formulas Into Cells
Macros that will insert formulas into cells, and I've been able to modify the macros to insert additional (simpler) formulas well enough on my own. But I can't seem to find the pattern of where to put the quotation marks! Here's the macro code that will enter my formula, including the formula (and the portion of code that the debugger highlights): r.Offset(, 1).FormulaR1C1 = _ "=VLOOKUP(rc[-1]"",""'Blend List'""!""A2"":""E250"",5,FALSE)" And here's the code in it's entirety: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Intersect(Target, Columns("d")) Is Nothing Then Exit Sub Application.EnableEvents = False ActiveSheet.Unprotect For Each r In Intersect(Target, Columns("d")) If r.Row 1 Then If Not IsEmpty(r.Value) Then...........
View Replies!
View Related
Gaps In Cells When Calculating Formulas
I have what is probably a simple problem for most, but can't figure out what to do. In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
View Replies!
View Related
Dragging Down Formulas With Inconsistent Cells
How figure out how to drag down a formula that looks like this for the first three cells (C3:C5)? =(SUMPRODUCT(Polls!D2:D5,Polls!B2:B5))/(SUM(Polls!B2:B5)) =(SUMPRODUCT(Polls!D8:D11,Polls!B8:B11))/(SUM(Polls!B8:B11)) =(SUMPRODUCT(Polls!D14:D17,Polls!B14:B17))/(SUM(Polls!B14:B17)) ...etc. Is there a way to let Excel know that I'm adding six to each cell, rather than it automatically adding one?
View Replies!
View Related
Duedate Not Working With Cells With Formulas
In range D2:D7 there are formulas with dates where message should return saying please check the date which is overdue but the codes does not work In range D8:D11, these dates are normal without formulas and works with the code DueDateFail.xls If I change the code in Sheet1 to D2:D7 the codes won't work but do work with D8:D11 because the dates in D8:D11 don't have the formulas like D2:D7 has
View Replies!
View Related
Formulas That Include Blank Cells
I can't figure out how to make this formula work because I need to use cell AG46 and it contains an IF formula that leaves the cell blank if the sum = 0. I keep getting the #VALUE! error. How do you create a formula using a cell that has an IF formula that could leave the cell blank? Basically this is what I need in cell I12: =sum(a12)-(ag46*8)
View Replies!
View Related
Count Nonblanks (Formulas In All Cells)
What formula would I use to count the nonblank cells, if all the cells within the range have formulas in them. For example, cells A1:A50 each have a formula. As a result of the formula in the cell, 10 of the cells have values or text while the remaining 40 cells appear blank. What formula do I use to count the 10 cells?
View Replies!
View Related
Referencing Merged Cells In Formulas
Take the following simple sheet. My data is in column A, and includes some merged cells. I place the formula INDIRECT("RC[-1]",0) in column B, and copy it down the sheet. I get the result as shown in column C, but I would like the result shown in column D! | A | B | C | D | --|-----------|------------------------|-----------|-----------| 1 | Bob | =INDIRECT("RC[-1]",0) | Bob | Bob | --|-----------|------------------------|-----------|-----------| 2 | Harry | =INDIRECT("RC[-1]",0) | Harry | Harry | --|-----------|------------------------|-----------|-----------| 3 | | =INDIRECT("RC[-1]",0) | Charles | Charles | | |------------------------|-----------|-----------| 4 | Charles | =INDIRECT("RC[-1]",0) | 0 | Charles | | |------------------------|-----------|-----------| 5 | | =INDIRECT("RC[-1]",0) | 0 | Charles | --|-----------|------------------------|-----------|-----------| I realise WHY this doesn't work (as Excel refers to the merged cells A3:A5 as just A3, and A4 & A5 don't essentially exist any more), but I would like to know if there's a way to get around it, and achieve the result in column D.
View Replies!
View Related
|
TRACKER
