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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
Delete Rows Based On Rules
I am trying to clear row with the following rules: the last cell must not contain 0, if 0 delete entire row. the first cell the the A row should be only three character long, if more delete entire row. Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me . the script is here, Sub Del_rows_with_zero_in_column_of_activecell() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Const StartRow As Long = 1 'Row to Start looking at Dim StopRow As Long Dim Col As Long
View Replies!
View Related
Randomly Select Cells Based A Couple Rules
I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters. So what I am doing is having excel designate equal amounts of playing time for each player each game. Is this possible. Can I base this off of previous games?
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
Help With IF Rules
I am creating a worksheet that I need to encorporate an If statement into. What I need to happen is when cell A1 has a number in it (1,2,3,4...) I need it to perform a function on cells B1 and C1. However there will always be information in cells B1 and C1, but I only need it to perform the function when the number in cell A1 is anything but 0.
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
Calculating Value, Based On Time
I am trying to write what I believe is a fairly simple set of calculations to determine non-qualified stock option values. Before this digresses into a discussion of the merits of Black-Sholes methodology, I must tell you that this is a "101" type sheet. I'm not an economist. The deal is that my sister (for whom I'm constructing this worksheet), gets employer stock options which vest at different intervals. In the past year, she's gotten some options that vest in two, four, and five year increments. I'm using Excel 2002, with the analysis toolpak, and stock pricing add-in from MS. Have today's date, date of option grant, and can easily compute time (in years) until options are fully vested. The trick is that options with a 2-year vesting period are 50% excerciseable in one year, 100% in two. Four-year options are excerciseable 25% per year for 4 years; 5-year are 20% for 5 years, etc. I'm trying to write a single date-driven formula to take each grant (individually), and divide it by the appropriate divisor (based on the current date), to figure out the current value. I can't seem to quite figure it out how to write a formula which can handle: Shares Strike Price Date of Grant Years Until Vested Value 100 26.57 1/4/2003 5 245 45.67 2/6/2001 4 Value should equal = (if today's price is grater than Strike Price) (Current Price * (Shares * (% vested which is based on "date of grant" + "years until vested")) ) - (Strike Price * Shares * (% vested which is based on "date of grant" + "years until vested") ) how the math to make the "% vested multiplier" work...
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
Calculating Cost Based On Several Factors
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago. I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information. For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month. Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month. Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
View Replies!
View Related
Calculating Average Based On A Condition
I need to calculate the average of values in a particular column if some other column contains a particular text. For e.g. I would like to calculate average age of all the people who belong to the Comapny "XYZ". Which means that I have two columns here one is "Age" and the other one is "Company". So For all the people who belong to the company "XYZ", I need to calculate average age. Company Age XYZ 24 ABC 26 ABC 34 XYZ 30 My average should be (30+24)/2 = 27.
View Replies!
View Related
Some Cells Not Following Protection Rules
I have set all cells in a worksheet to be protected. I then selected specific cells that are not protected. The problem is that cells A11:D29 except C13:C14 do not follow the protection rules in that I can edit them (but when I right click, I cannot format, just like they're protected). These cells are NOT part of the group that was selected to be unprotected. When I unprotect the sheet and check the protection setting for these oddball cells, it shows as protected. All other protected cells do not allow entry (which is what I want).
View Replies!
View Related
InputBox Validation Rules
I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.
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 Total Based On Cell Contents
I'd like excel to calculate 3 totals for me based on the colour and value on a worksheet. Basically, I work for various people and they pay at different rates per hour. I currently have a spreadsheet with their names, times, and rates (see attached for example), but I calculate the amounts paid and due manually. If possible I would now like excel to do it. To explain further, 'J' gives me $10 per hour, and 'V' gives me $5 per hour. Cells shown in red show work done but not paid for. Cells shown in green show work done and paid for. I'd like excel to automatically create totals as shown on the spreadsheet, namely: Total due: xxxx Total paid: xxxx Total outstanding: xxxx At any time during the month I can be asked to take on more work - I would then enter the code into the spreadsheet for the hours requested...and I'd like the totals to be update automatically.
View Replies!
View Related
Calculating A Range Of Values Based On 2 Factors
I need a way to calculate building rates based selecting the building and the value selected; I have a list of building construction (fire resistive - protected, non combustible - protected etc)... in cell N8 and the limits in cell O8. Now what I need to do is calculate the rates in the file attached (cells A5:J21 in my worksheet) based on the building construction and the value/limit I select.
View Replies!
View Related
Calculating Percentage Based On Dynamic Cells
I need to calculate the percentage based on self populating score. however I want excel/formula to ignore any cells which has "NA" in it so that it calculates the percentage based only on the cells having Numbers. Now these Cells which contain Numbers might or might not contain numbers based on the "IF" function which decides wether the cell will have numbers or will remain "blank" or have "NA". To understand better please have a look at the enclosed sample (that would explain my need better)
View Replies!
View Related
Calculating A Total, Based On Values In Other Cells
Using Excel 2002. Here's my problem. Column A contains the month (as text) Column C contains an employee name. Column O contains a reason for absence. Column K is the number of hours of absence. The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence. E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X. This will be used on a seperate worksheet where the name C will be referenced from a validation list.
View Replies!
View Related
Custom Rules For Sheet Naming
I want something strange but i guess you can do just about anything with excel these days if you ask around. I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following: sheetname = runxyy_s=zz% Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.
View Replies!
View Related
Different CONCATENATE Rules Depending On Cell Value
I'm trying to do a CONCATENATE function in Excel 2003. The first part of the function (this is working correctly) is a) concatenating 4 cells from a different worksheet (WB_NEW) into one cell and is b) putting a space in between each entry: =CONCATENATE(WB_NEW!AH3," ",WB_NEW!AI3," ",WB_NEW!AJ3," ",WB_NEW!E3) What I'm now trying to do is enhance this function to perform with 2 separate concatenation rules i.e. "if one of the cells specified above is blank, concatenate a different set of cells".
View Replies!
View Related
Permitted Permutation/Combinations With Some Rules
I have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from. What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to: 1) Permutation can't have both S and PP, so one or the other 2) Sum of H and HN must be greater or equal to sum of S and PP 3) H or HN or both must be present, so their sum cannot be zero 4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.
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
Calculating Hours Worked Based On Elapsed Time Over 24H
If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked. It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work. A2=04:00 A1=23:00 Using (A2-A1)*24 give me -19.00 hours My SS macro has a line: s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to 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
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
Calculating Time Owed Based On Time Worked
I know the title is a bit vague, but I cant think how else to word it! I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target. I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
View Replies!
View Related
Calculating Cells Used Based On Another Cells Data
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example: Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit. Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit. The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had. So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc. I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
View Replies!
View Related
How To Validate If Data In A Cell Conforms To Data Validation Rules In The Cell
I have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number. I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically. [url] To keep it simple, I'm only supporting pasting a single cell at a time. Now my problem is this: Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List. The following post just suggests disabling paste whenever data validation is present: [url] But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.
View Replies!
View Related
Calculating % GP Anyone
im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100-100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver.
View Replies!
View Related
Calculating Months
I input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.
View Replies!
View Related
Calculating Using Time
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.
View Replies!
View Related
Calculating TAT
I have some activities that needs to be done ina specified time Like for example TAT HRShrsACT 14ACT 28ACT 324ACT 42ACT 54 And i have a sheet where in i need to calculate turn around time ActivityStart timeEnd timeTATACT 1ACT 2ACT 3ACT 4ACT 5 Based on time entered in start time and end time the TAT column should calculate the TAT based on the above standards. I also would liuke to exclude weekends(Saturday, sunday). Also the TAT should only be calculated during working hrs between 7:30 PM to 4:30 AM.
View Replies!
View Related
Calculating Cells ..
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it. Is there a way that I can speed this process up, using some code when I run certain sections? There is a copy of what I am doing, so you can see the amount of formula.
View Replies!
View Related
Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network. Edit: Charges to network A is 1,79,- per minute after the first 20 minutes are spent. Charges to network B is 2,29,- per minute after the first 20 minutes are spent. To sum up: 1. The customer makes a call. 2. If there there are available free minutes, these should be spent first. 3. The customer is charged per minute, depending on network called.
View Replies!
View Related
Calculating Median
I have a list of hotel IDs in one column of my spreadsheet. In the next few columns, I have ratings that customers gave the hotel for cleanliness, location, room, etc. What I need to do is calculate the median of all the ratings for each specific hotel, in a separate column. An example of what I have: ...
View Replies!
View Related
Calculating Time In
=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1)) i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error so what im trying to do is base on the system time is if the person typed in if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time. 6:55am gets $5 7:00am they get $5.00 for being on time if they type 7:01am to 7:05am they get $3.00 if they type 7:06 to 7:10am would return You Were Late after 7:10am would return No Pay so Ex D4 *answers in E4 * 7am would get $5 7:03am am would get $3 7:07am would get You Were Late 7:11am Would get No Pay if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/
View Replies!
View Related
Calculating The Ballots That Come In
I am on an auto show board and want to come up with a way of calculating the ballots that come in, using excel. Ballot points are accrued as follows: 5 points for 1st place, 3 points for 2nd place, 1 point for 3rd place. With about 25 different categories (classes) and I am estimating participants at around 100.where I can in the automotive field knowing there is nothing besides personal gain.
View Replies!
View Related
Calculating The Overtime
We are guaranteed (right now) a 10 hour day. (we are on 4 - 10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day. Mon 10 Hours Tues 11 Hours Wed 9.5 Hours Thurs 10 hours I should get: Mon 0 overtime Tues .5 overtime Wed 0 overtime Thurs 0 overtime I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file.
View Replies!
View Related
Calculating Days ..
What I would like to do is insert a formula into column E that would calculate the number of days from when the count is at 50 to when the next count is i.e. for part 6689841 the time between when the count was 50 and when it was at 20, so the difference between 21/01/2008 and 08/08/2008 in the above example. It does not matter what the count number is (it could be any number except 50), I'm only concerned with every time the count is at 50 how long it is until the next count and to show the result in days.
View Replies!
View Related
Calculating A Formula
i need to creat a 12 month calculation of accounts month 1 month 2 based on a precentage so i need to calulate the cell 1 pluse a precentage equals cell 2 and then cell 1 plus 2 and the precentage equils that
View Replies!
View Related
Copying But Not Calculating
I'm copying a formula and pasting it to a group of cells. It copies correctly, but I DO NOT want it to calculate anything until other input cells are filled in the columns for the rows that I have pasted the formula. I would like it to show "nothing" until other data is inputted.
View Replies!
View Related
|
TRACKER
