Conditional Statements Using IF: Get A Discount (no Discount) Not To Apply The 10%
Dec 22, 2009
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 4 Replies
ADVERTISEMENT
Jan 14, 2014
A customer has an overdue account, we will say in the amount of $500.00. We offer various discounts, 30%, 20%, 0% depending on the circumstance.
What I had previously worked flawlessly, that is until the higher ups changed how things had to be calculated.
Previously, we would take the amount owing and apply the discount (ie 20%) which would become the new amount due ($400).
We would then take the discounted amount and separate it into monthly payments:
$400.00
1st payment 60.00
2nd payment 60.00 (and so on)
6th payment 60.00
7th payment 40.00
Done.
The new way they want it calculated is (Same situation $500 owing, 20% discount).
$500.00
1st payment 60.00
2nd payment 60.00 (and so on)
6th payment 60.00
7th payment 60.00 *
8th payment 60.00 *
9th payment 20.00 *
Done
(*these would be the discounted amount, it would be stated that these payments would be removed if payments are made on time and in full yadda yadda).
The trouble I am having, is figuring out a way to tell excel to flag the specific payments. The reason behind it (i guess) is so that if a client becomes delinquent, the full amount owing would be sent to collections, not the discounted amount owing.
View 1 Replies
View Related
Jul 13, 2009
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 6 Replies
View Related
Dec 24, 2007
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 5 Replies
View Related
Feb 6, 2009
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 2 Replies
View Related
Dec 3, 2009
how to get the discount to work the way I need to. As is, It requires a person to put in the actual amount that I will discount the product for each product sold. I would like to simply key in the discount percentage. EX: 10% or 13%
View 14 Replies
View Related
May 8, 2014
If I explain all the 8 steps , it will be lengthy and not easy to explain , thus I will go step by step
See file attached raw 01 , copy it on drive c directory called DISC , see file attached the result file
I want the the result file to go fetch data from the folder created DISC in which there is file called raw 01 and do the following with pulling headers , since the header is already plugged.
a to a
b to b
c to c
d is thus empty for the second step of the macro ( explain following after this fetching is achieved )
d to e
e to f
f to g
g to h
h to i
j to k
k it should not copy
View 5 Replies
View Related
Mar 30, 2007
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 11 Replies
View Related
Jun 25, 2014
I need a formula to take away a percentage according the amount (number) ot procedures.
For example:
- procedure - price for one - 5 of it - 10% discount - final price
The idea is, for example, if I type amount of 5 procedures, automaticly to make 10% discount, if I type 10 procedure - make 15% discount etc.
View 6 Replies
View Related
Feb 11, 2009
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 7 Replies
View Related
Nov 9, 2013
I'm very new to excel, my boss request me to create a discount calculator. A cost of price is given. We have a fix discount rate of 5% of that cost value. Some new customer want more percent of discount to the item they purchase. To deal with that, a markup price will be quoted then we will make a discount according to what the customer prefer but the final discounted value will be always be the discounted 5% of our cost value. I've problem creating it because as example; the customer request for 15% discount, when I add the cost + 10% then minus it back to 15%, the value I get will no be the same of the fixed reduced 5% value of my cost value.
ac16xpZ.jpg
View 5 Replies
View Related
Apr 7, 2008
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 9 Replies
View Related
Jun 7, 2006
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 2 Replies
View Related
Jul 10, 2014
I want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.
Example:
Column I=7.99
Column M=7.19
Column I=7.59
Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)
Column I=2.19
Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)
View 3 Replies
View Related
Jan 11, 2009
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 3 Replies
View Related
Jul 14, 2014
How would I create a simple user form to display a price based off the category selected.
User types in List Price, then selects 1 of 4 discount categories, it then displays that price.
An added bonus would be copying that price to another sheet, but not necessary..
View 2 Replies
View Related
Apr 25, 2006
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 9 Replies
View Related
Nov 23, 2009
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 5 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Sep 14, 2012
I am trying to create a macro (which will go in an add-in, using Excel 2007) which will apply a custom format to any selected cells which have their formulae hidden (Format Cells, Protection, Hidden). A similar macro works fine for locked cells.
Here is the UDF I wrote, which returns True/False based on the Hidden status of a cell:
VB:
Public Function Hidden(Check_Cell As Range)
Hidden = Check_Cell.FormulaHidden = True
End Function
[Code]....
just place all three pieces of code into a module, and change the first line of the two macros to a standard "Sub Macro1()" type format.
View 9 Replies
View Related
Jul 3, 2014
I have a workbook that contains 168 sheets of data (it's an extract from a PM tool) which is effectively a status report from each project in our portfolio. Contained within each status report are some financial data that shows a Plan number and a Forecast number for which I want to apply conditional formatting to this section (this is the same section for each sheet), to all the 168 sheets without having to go individually into each sheet. I have searched here and all the varying responses to a similar situation as mine, do not cater for the number of sheets that I have. And I need to do this on a monthly basis at monthend. So in the example below I want to apply conditional formatting if the Forecast (Cols D & G) are greater than Plan (Cols B & E). Is there a way of doing this just with the conditional formatting or would it need a VBA script?
Col A Col B Col C Col D Col E Col F Col G
Financial Summary - Selected Project Currency: USD
Current year total cost
Overall project cost
[Code] .....
View 1 Replies
View Related
May 10, 2013
I have a column in a sheet referencing data from a column in another sheet in my workbook. I want to apply conditional formatting on these numeric values, from red for lowest vals to green for highest. However the fact that my column is referencing cells that will not yet have data means that they are being pulled over as 0. I am not displaying zero values in cells but the zero vals are still being included in the conditional format. Is there any way to discount these zero vals?
View 2 Replies
View Related
Dec 31, 2013
I wish to have one conditional format apply to K and L. Presently it's L only where it works.
=OR(K1="",AND(K1="No",L1<>""),AND(K1="OJ",L1=""))
Why when I change the range from =$L:$L to =$K:$L does it not apply to both?
View 3 Replies
View Related
Feb 4, 2014
is possible to construct a conditional formatting scenario and at the same time apply that formatting if the cell entry is within 10% either way of the logical test?
For example if you apply conditional formatting if the cell value is between 300-500 is it possible to add in a 10% swing on each value?
View 3 Replies
View Related
Jun 27, 2014
I have a conditional formatting issue, using VBA.
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
My code is below.
Code that applies to column "M" only...
View 3 Replies
View Related
Feb 27, 2012
I recorded a macro in which I applied a conditional format to a range (the selection).But, when i replay this macro, the conditional format is only applied to the first cell in the range. This can be seen in the "Manage Rules" dialog of Conditional formatting, where the "applies to" column shows only one cell, even though a range of cells was selected by the code.
I set a breakpoint at the first line of code here, and confirmed that the selection is correct, and indeed it is, as in the immediate window, ?Selection.Address correctly gives "$O$6:$O$21".
[BRK, Selection.Address="$O$6:$O$21"]
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=O6/(O6+P6)>25%"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16
[code]....
why the conditional format ends up only applied to cell O6 after the code runs?
"How can i apply a conditional format to a range of multiple cells, based on an expression, using VBA?"
Note that my range is within a PivotTable (but still, this works fine in the UI when i record the macro and the conditional format is correctly applied to the entire selected range).
View 1 Replies
View Related
Aug 12, 2012
I have a conditonal format of cells across Row 2 based on the contents of cell D2. The formula for the conditional format is
=$D$2="NONE"
The formatting applies to these cells:
=$F$2:$G$2,$I$2:$K$2,$M$2:$N$2,$O$2,$T$2:$V$2,$X$2:$AA$2
I would like to fill in the same conditional format down to 51 other rows but the formatting for each row should be based on the contents of its own cell i.e. $D$3, $D$4, $D$5 etc. How can I do this with code?
View 2 Replies
View Related
Feb 29, 2008
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 9 Replies
View Related
Aug 14, 2009
Trying to use several ifs together. Not sure, where my end if's need to be. I keep get Next w/o For error.
View 4 Replies
View Related
Mar 31, 2013
I was wondering if is possible to apply a conditional format rule to a range of cells only when certain other cells outside of the range are the active cells. eg row C4-J4 has a conditional format to identify duplicates from range C5-C10, but I also want the same conditional rule to apply separately and uniquely if the active cell falls within the next active range D5-D10. So basically only apply conditional formatting separately and uniquely as the active cell moves its way across the columns?
View 3 Replies
View Related
