Discount Applied To A Cell And Rounded To Nearest Nine
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)
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%?
How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
If you run it type in 3.5. What gets returned from the procedure is the number 4 NOT 3.5. I think its related to the data type declaration but i thought a data type of long would include the number 3.5.
How can I add rounded corners and a drop shadow to the code below for a graph.
Code:
Sub AddAICharts() Dim co As ChartObject, endf%, i%, r$, sname$, suffix '~~~ Suffix allows the code to be manipulated more easily with changing the sheet name but keeping the Suffix the same suffix = Array("A", "B", "C", "D", "E", "F", "G", "H") For i = LBound(suffix) To UBound(suffix) '~~~ name of sheet is currently set for Objective sname = "Data" & suffix(i)
I'm trying to apply the code for accumulating value in a single cell to a range of cells.
I want to use the code found in this thread [URL] ..... but, it only lets me edit "B1" to another single cell. I need my cells G4 to G469 to operate the same way.
How to edit the code so I can include a range or show me a version of the code where I can enter my desired range?
The above formula is not working as in A9, I have a formula although there is no physical text in the cell as the criteria applied is false. However, B9 still appears.
I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.
I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros
I have a list in column A that goes [Blue,0,0,0,Green,0,0,Orange,0,0,0,0,0, etc.] The number of 0s is not in any pattern. Based on column A, I would like column B to go [Blue, Blue, Blue, Blue, Green, Green, Green, Orange, Orange, Orange, Orange, Orange, Orange,etc.].
So, I would like column B to look at column A, and if it is a 0, return the nearest color above. I was thinking of maybe using a whole bunch of imbedded "if" functions that would keep going up and up if it was 0 and then return the cell when it's not 0, but sometimes it might be 30 0s, so I'm not sure that's the most efficient way to go about it (or maybe it is and I'm not thinking about it right). Does anyone know a nice neat little function that would work? I've attached a sample spreadsheet that illustrates what I'm trying to do. Column A is what I start with and Column C is where I'd like to be.
In cell A1 I have a number. I want a formula that looks at that number and then looks at row C on sheet2 where there are rows and rows of numbers. I want the formula to return the nearest match to the number in cell A1.
I have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.
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.
Assume you have a rectangular range. Say A1:M18. There are values in some random cells (any number of cells) within the range. The rest of cells are blank. In cell M19 (ie cell below the bottom right corner of the range), I need a excel function that returns the nearest (ie physical location) non blank cell value. I have searched the internet for weeks. The closest formula is the following (see below) but it is flawed as it returns zero if there are values in rows below the row of the nearest cell with non blank...and also zero if there is value in a column greater than the column of the nearest cell with non blank.
Closest formula so far: INDEX(A1:M18,LARGE(IF(ISBLANK(A1:M18),"",ROW(A1:M18)),1)-ROW(A1:M18)+1,LARGE(IF(ISBLANK(A1:M18),"",COLUMN(A1:M18)),1)-COLUMN(A1:M18)+1).
And the function should be relative not absolute as the range can be extended..but the location of the formula remain same relative to the array size. (ie. always at below the right bottom cell).
NOTE: Ignore the special case where two non blank cells are the nearest. That will not happen.
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.
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%
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
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
(*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.
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?
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.
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 +
I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.
I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.
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.
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..
