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 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
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
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
Macro To Apply Conditional Formatting
I'm trying to find a macro to apply conditional formatting to a large number of cells, What I would like to do is when cell E96 has a value of a, cells E3:F95 are shaded in grey. Then when cell G96 has a value of a, cells G3:H96 are shaded in grey, and so on down to IU96 having a value of a and cells IU3:IV96 shaded in grey.
View Replies!
View Related
Easily Apply Conditional Format Formula To All Rows
i have the following formula applied to a conditional format, on row 12: cell value > less than =($K12:Y$111)*(1-0.05) i want to use this on all rows, however i have 300+ rows. Is there a way to apply this to all rows between column K and Y, i don't like the thought of creating 300+ conditional formats!
View Replies!
View Related
Apply Conditional Formatting To A Cell When It Is Left BLANK
I'm trying to apply conditional formatting (shading) to cells that are left blank. (Purpose: I am designing a research template for a client to complete with data and want the spreadsheet to show them where they've "missed a bit"!) (When I go to the conditional formatting box, it asks me to specify when "cell value is"..."between/not between/equal to/not equal to" etc. But there's no option to specify when the cell is blank.)
View Replies!
View Related
VBA To Apply Conditional Formating To A Range Of Cells
I have used VBA to apply conditional formating to a range of cells. i.e if cell B14 <>"" then row 14 is pale blue untill cell V14 is populated with the time then it removes the formating. One criteria is that if Q14(21/08/06 20:00)>NOW(AA2) and < NOW+1(AA3) then Row turns green(i.e. is due in the next 24 hrs). Problem is that this formula is applied after a field is updated. When 21/08/06 20:00 comes and goes the row remains green unless I update one of the fields along that row. What would I have to do to make the formating change back automatically when the critera is no longer being met. Here is my codethere are 2 other if statements similar to this with in this code but this is the only part that shouldn't require any user input for the formatting to occour) Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B13:AP162" On Error Goto ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "AP").Value = "YES" And _ Me.Cells(.Row, "Y").Value <> "" Then Me.Cells(.Row, "B").Resize(, 30).Interior.ColorIndex = 43 'mad green Else End If End With..................
View Replies!
View Related
Apply Conditional Formatting To Bars In Bar Charts
I have created an excel file that contains a lot of worksheets which contain a lot of raw data and charts. There are 5 different tabs for 5 different companies. Each tab currently contains only 1 year of raw data for that company. Then I have another excel worksheet that has nothing but charts on it. Each chart is a bar chart that shows each of the 5 companies. It also shows the current rate for the month for each company and the 12 month average for each company. On the chart there are also 3 lines. There is a standard line that we try to meet each month. There is also a line for the upper control limit and a line for the lower control limit. Here is my problem: The 2 bar charts (current rate for the month & the 12 month average) are created and updated by the raw data in each of the 5 data sheets. Based upon the values in each of the cells that create and update the bars on the charts themselves: I want the bars to be RED if the values are below the lower control limit. And I want the bars to be GOLD if the values are above the upper control limit. And I want the bars to be green if the values are in between the upper and lower control limits.
View Replies!
View Related
Conditional Formatting With IF/Then Statements
I need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on. The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46
View Replies!
View Related
Conditional Formatting With If Statements
I have a spreadsheet that has several columns containing names and data. In the first column, is a person's name, in the second, is a client number for that person, and in the third is the "last contact" date. I have a fourth "reference" column that has a conditional statement I was trying to use for formatting. For example, in A1 would be "John Doe," in B1 would be 987, and in C1 would be 06/10/07. The next row would have data for a different person. In column E I have my reference column. If the last contact date is less than 4 days from the current date (which is in cell K2), the reference column (using an if formula) will produce the number 1. If it is 5 or 6 days from the current date, it produces the number 2, and if it is 7 or more, then it produces the number 3. Here is the exact formula: =IF($C2+7<=$K$2, "1", IF($C2+5<=$K$2, "2", IF($C2+4>=$K$2, "3"))) What I want to happen is this: if the reference column produces a 1, then I want the corresponding row to turn red. This would only include the name, assigned number, and last contact date. I do not need the entire row to change a certain color. If a 2 is produced, I would like the row to turn yellow, and if a 3 is produced, I would like the row to turn green. This is going to run for many rows (up to 150). How do I do this using VBA? Keep in mind the numbers in the reference column will change if the "last contact date" is updated to a more current date.
View Replies!
View Related
To Get Average Or Count Of Column With Conditional Statements For Date
The data: There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date. The formula needed: I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on. My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data. The furthest I got was this, but it didn't work. =AVERAGE(IF('A3:A33>=VALUE(D3)
View Replies!
View Related
Extending Previous 'conditional Statements Solution'
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution: http://www.excelforum.com/excel-gene...-new-post.html I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
View Replies!
View Related
Conditional Format - Can"And" Statements Be Used?
Let's say I have a cell which has a value between 0% and 100% and I want to set three criteria to change colors. The problem is that I only have three conditions that are able to be set and recognize that I need the first to remain as is, so that it stays white if no data in there. Can it be done without a macro? For instance: Condition 1 - Cell Value Is - ("") - the result is shaded white (this works) Condition 2 -Formula is - =F8>=(0.95),(and(F8<(0.99) - I want this green, but it isn't taking it? Can you put multiple statements in a conditional format if the answer is either true or false? Condition 3 - Cell Value Is - <95% - Turn Red (This Works)
View Replies!
View Related
How To Apply Conditions On Charts
I'm new to using Macros and vba code in Excel and am getting problems in creating dynamic charts. Although I had seen all the posts of condional charts in search forum but no use. I have an excel sheet with Date Received Column,Volume Unprocessed and the third column is to add 5 working days in the date received.I have to ask three questions.Please find enclosed the attachment. 1)In the attachement ,the excelsheet has 4 rows and chart for these values is displayed in chart1. But when new values are added up,it doesn't show those values in the chart automatically and I think I need macro for this. 2) I want macro to automatically calculate date by adding 5 working days in the date received for the rest of the rows. 3) If the current date is equal to date in the third column then the bar should be in orange colour. If the current date is greater than the date in 3rd column then the bar should be green colour and if its less then bar should be red.
View Replies!
View Related
Apply If To Range Of Cells
How do I change this formula to represent a range? I want cells C2:C18, but I ran out of arguments - can't add any more. If DRC or Production shows in the column, I want the word "Outstanding" if not, I want the word "Completed" .....
View Replies!
View Related
Apply Ranking By Date
I need to add a field to a data set that ranks groups of data based on a date field. Items with the same "Type" & "Vintage" need to be assigned a number based on the date field starting with 1 for newest to x for oldest. I'm looking for a macro or formula that I can use to automatically assign these values. I attached an example. The highlighted green section was completed manually for reference. The real data set is thousands of lines.
View Replies!
View Related
Apply Macro To All Workbooks
Could somebody update the below code so that it actions all open workbooks except the master (Book2). Also, so that it appends the data (which is basically 2 neighbouring colums of data) to the Master starting in column A Sub Format_RAW() ' ' Format_RAW Macro ' ' Keyboard Shortcut: Ctrl+s ' Selection.AutoFilter ActiveSheet.Range("$A$1:$P$5105").AutoFilter Field:=7, Criteria1:=Array( _ "H1", "H2", "H3", "H4"), Operator:=xlFilterValues ActiveSheet.Range("$A$1:$P$5105").AutoFilter Field:=1, Criteria1:= _ "Tu" Range("G7:H7").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("Book2").Activate ActiveSheet.Paste End Sub
View Replies!
View Related
Apply To All Sheets Not Working
why the "apply to all worksheets" portion of this code is not working? I appreciate your time. Sub Delete_0activityaccount() ' ' Dim mywSheet As Excel.Worksheet For Each mywSheet In ActiveWorkbook.Worksheets lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = lastrow To 8 Step -1 If Cells(i, 4).Value = 0 And Cells(i, 5).Value = 0 And Cells(i, 6).Value = 0 And Cells(i, 7).Value = 0 And Cells(i, 8).Value = 0 _ And Cells(i, 9).Value = 0 And Cells(i, 10).Value = 0 And Cells(i, 11).Value = 0 And Cells(i, 12).Value = 0 _ And Cells(i, 13).Value = 0 And Cells(i, 14).Value = 0 And Cells(i, 15).Value = 0 And Cells(i, 16).Value = 0 _ And Cells(i, 17).Value = 0 And Cells(i, 18).Value = 0 Then Rows(i).Delete Else End If Next i Next mywSheet End Sub
View Replies!
View Related
Apply Same Formats To All Sheets But One
I'm trying to apply some settings to all sheets in my workbook except the first sheet called Total. For some reason, the settings are not applied to all sheets but simply stays on the sheet I select. Also, how can I ensure the changes are only applied to the sheets: A, B, C, D etc. but not to Total? Sub Test() Dim ws As Worksheet For Each ws In Worksheets Cells.Select With Selection.Font .Name = "Calibri" .Size = 8 Columns("H:H").Select Selection.ColumnWidth = 35 Columns("I:I").Select Selection.ColumnWidth = 12 Selection.NumberFormat = "#,##0.00" Columns("J:J").Select Selection.ColumnWidth = 12 Selection.NumberFormat = "#,##0.00" End With
View Replies!
View Related
How To Apply Macro To A Cell
I've made a spreadsheet at home on Excel 2007 which has up to 5 Conditional Formatting rules per cell which works fine. The problem is when I save as 97-2003 version to send to work, the old version of Excel only supports 3 rules as you know, is there a way of applying 4 or 5 rules when specific text is populated in a cell on the old version of Excel?
View Replies!
View Related
Apply A Curve To A Column
I'd like to apply a curve to a column of student exam grades and have the curved grades displayed in the adjacent column. Here is how I'd like to curve: I like to make a linear function out of two coordinate pairs. First Pair (x1,y1) = (Highest Exam Grade, 99) = (H,99) Second Pair (x2,y2) = (Class Exam Average, Desired Exam Average) =(C,D) Slope = (99-D)/(H-C) = m Linear Function = Y-y = m(X-x) = Y-99=m(X-H) Each student grade would = "Y" i.e. Y=m(X-H)+99
View Replies!
View Related
Apply Macro To All Worksheets
I need to run the below code on all work sheets: Sub Remove_Duplicate() On Error Resume Next Columns("a").Insert With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(, -1) .Formula = "=If(CountIf(B$1:B1,B1)=1,"""",False)" .SpecialCells(-4123, 4).EntireRow.Delete End With Columns("a").Delete End Sub Basically I have lots of worksheets and I want to remove the duplicates in column B for each work sheet with a duplicate entry...
View Replies!
View Related
Apply Macro Between Tabs
I have a worksheet that I am always adding sheets to by duplicating an already existing tab. The tab ,"Current Invoice", is nested between sheets called "first" and "last". All duplicate sheets will also be nested between the "First" and "Last" tabs. What I would like to do is have a macro that affects all tabs between the "First" and "Last" only, even when the bookend tabs are hidden. This is what I've started with. HTML Sheets(Array("First", "Last")).Select ActiveWindow.SmallScroll Down:=18 Range("U29:U190").Select Selection.Copy Range("AA29").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("W29:W190").Select Application.CutCopyMode = False Selection.Copy Range("AB29").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
View Replies!
View Related
To Count And Apply To Macro
I'm Working on the next part of my long term project. I need to count the number of Cells Selected in MacroTest2.xls column BG. ( Selection.count) ? I need to Open Workbook MacroTest3.csv. It has a Header and only Row 2 filled with data. (Windows("MacroTest3.csv").Activate) I need to Pulldown/Copy row 2 a number of times so that the Number of Duplicate Rows Created is Equal to the number of Selected Cells in the Count in MacroTest2.xls. I have been able to do it based on a Static Number of Selected Cells (3) ( the existing Row + 2 ) by recording the manual event with this code. Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:4"), Type:=xlFillCopy Rows("2:4").Select I'm trying to understand how to declare the variable and apply it, so that the Number of Rows of Template Data in MacroTest3.csv is Equal to the Number of Selected Cells in MacroTest2.xls column BG. This is part of a Macro that will then transfer some same selected Range of Cell Data as well. It is Currently working but not with a variable number of selected cells. I can post the whole code if it would help you to see the whole picture.
View Replies!
View Related
Macro Apply To Next Row
I am trying to record a macro that edits a rows data, and simply copies it to a new cell further along on the same row,. but i then want it to move to the next row down, and apply the same macro to that run, and continue until there is no data in the last row,. How do I get the macro to continue to the end of the data.
View Replies!
View Related
Apply $ To Range Of Cells
Is there anyway to apply the $ to cell references in formulas across a range of cells? For example, I currently have the following in Column A: =AAA_SR_F_1!$G5 =AAA_SR_F_1!$G6 =AAA_SR_F_1!$G7 And the following for Column B: =BBB_SR_F_1!$G5 =BBB_SR_F_1!$G6 =BBB_SR_F_1!$G7 Is there anyway to apply the $ to the entire range listed, i.e. AAA_SR_F_!!$G$5, etc.
View Replies!
View Related
Apply Filter Then Subtotal
I have a spreadsheet where users will filter it using autofilter. I then have two cells where the val/volume of each account is totalled, this works fine. However I would also like to be able to say which is the most recurring product within the account and how many times it occurs. I have attached my sheet below, I hope this helps. Eg, Account 1 worst product is..... with a volume of...
View Replies!
View Related
Apply Formatting To All Worksheets
I have this macro and all I want to do is apply it to every page of my worksheet with a loop and can't find what kind of loop I need: Sub format() ' Keyboard Shortcut: Ctrl+r ' ActiveCell. Offset(-37, 0).Rows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(37, 0).Rows("1:1").EntireRow.Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Range("A1:K37").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With
View Replies!
View Related
Apply Macro To Some Sheets
i used your recommended summary page that you posted somewhere in ozgrid. i have this workbook which has 6 worksheets. 1st sheet: summary page. this adds all the sheets between top and bottom 2nd sheet: "TOP" 3rd sheet: "Red" 4th sheet: "Blue" 5th sheet: "Yellow" 6th sheet: "BOTTOM" i have this macro which performs some copy-paste-compute codes. my problem is this: i don't know what codes to use so that the macro will be applied to only those sheets between TOP and BOTTOM...
View Replies!
View Related
Apply Macro To Protected Sheet
I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying: "The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password." The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting Application.DisplayAlerts = False into the code, but it does not stop the message.
View Replies!
View Related
How Can I Limit This Macro To Apply Only To A1:A37
I poked around and found a macro to highlight the active cell, but I want to limit it to be active only for range A1:A37. Any ideas? I put in the Set OldCell = Range("A1:A37"), but all it does is turn every cell you pick blue and it stays that way, so I'm not sure how to limit it. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldCell As Range Set OldCell = Range("a1:a37") If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = xlColorIndexNone End If Target.Interior.ColorIndex = 8 Set OldCell = Target End Sub
View Replies!
View Related
|
TRACKER
