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 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
Formula To Calculate Percent Change, Varied By Amount Of Months
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View Replies!
View Related
Mark Up In Advance The Dates Where I Get Paid
I'm trying to mark up in advance the dates where i'll get paid. I get paid monthly on the 2nd last working weekday of the month. So because we have the 30/11/2009 falling on Monday of next week, my pay day for this month is Friday 28/11/2009.in December it should be the 30/12/2009. I'm getting all tied up in Networkdays, EOm and weekday function!
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
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
IF Statement- Spreadsheet To Calculate Referral Fees Paid To Our Agents
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges: Up to $5,000,000 (I got this one to work) $5,000,001 to $7,500,000 $7,500,001 to $10,000,000 $10,000,000+ I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
View Replies!
View Related
Calculate Probabilities In Percent
I have had a fascination with the lottery, purely hobby, and have had lots of fun over the years working different things out. The last 6 months though I have become fascinated with roulette & thought it would be a fun project to work out all sorts based around that, plus I don't have to wait for lotto results I can get instant numbers & results, however my latest attempts are hitting a brick wall! I am trying to work out (in percentages) the increasing & decreasing % of 3, 11, 12, 22, possible outcomes I have worked out the 2 possible outcomes initially for odd/even as follows At the start they both have a 48.65% chance of hitting, then whatever is hit first the percentages are 76.33% and 23.67%. If you have 2 in a row of odd/even then the percentages are 88.49% & 11.51%, 3 in a row would give you 94.40% & 5.60% etc. I have used the following formula for this (BM5 is where the totalhits for even are calc'd) ...
View Replies!
View Related
Calculate Percent Of In Pivot Table
looking for a way to run some pivot tables on a large data table. Would like the result to show some different data extraction from the same field / column. The table is customer survey results for my employees, and the fields in question can have values from 1-5. I would like to finish the pivot table with all of these fields: Row: Name (ok, that part is easy) Data fields: % of entries (column 2) that are 5 % of entries (column 2) that are 4 or 5 % of entries (column 2) that are 1 or 2 # of entries (column 2) % of entries (column 3) that are 5 % of entries (column 3) that are 4 or 5 % of entries (column 3) that are 1 or 2 # of entries (column 3) I'm hoping this is something I can do with calculated fields, but haven't been able to figure it out. So far all I have is a 'Count' function in the pivot wizard for the # of entries, but I'm not getting the % of entries at all. Column A = Name, Column B = 1st metric, Column C = 2nd metric. Fairly simple layout, but I have a small sample file I can attach if that's not explanatory enough.
View Replies!
View Related
Calculate The Percent Of People Within Age Range
In the demographics sheet, I have ages listed from row F2 to F31 with different ages. I would like to get assistance with a formula that calculates the percentage of people within these age ranges: 21-25 26-30 31-35 36-40 41-50 51-59 60+ It should be separate formulas. I'm sure if I'm given the first and last ones that I could do the others myself. Also, if I needed to know the percent of males and females, would i use the same formula?
View Replies!
View Related
Formula To Calculate Percent Difference Between Last 2 Columns
See attachment. In this example, in Column D I want to calculate the percent difference between the numbers in the last 2 columns (Column B and Column C). BUT I want a formula that will automatically update if I were to insert a new column between Column C and Column D. So as a result, new numbers would go in Column D and the percent difference would now be in Column E.
View Replies!
View Related
Calculate Number And Amount Of Payments
The example: Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used. Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00 Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments. So the sort of result I'm looking for would be like Assume todays date is 19-04-09 A B C 12-04-09 £5.00 Last Week 4 Payments Value £45.00 12-04-09 £10.00 13-04-09 £10.00 14-04-09 £20.00
View Replies!
View Related
Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine. I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy. What I have so far: p*(1+(r/100))^n Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan? Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!) To keep it short i followed this guide for the amortization table. and now I am so confused about if I should be using PMT, PPMT, NPER?!
View Replies!
View Related
Calculate Sick Days
I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job Please find attached a sample sheet of where I’m up to. I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP. A6:A735 have set dates for two years D6:D735 have the sick days an employee has taken B6:B735 Matches the sick date to the date in column A G2 has how many days entitlement for CSP H2 has how many waiting days (W D) before CSP can start In Column C i need CSP, SSP, or W D next to the number 1 And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.
View Replies!
View Related
Calculate Number Of Days Between 2 Dates
I have a start date formatted as dd/mm/yyyy in cell A5 I have an end date formatted as dd/mm/yyyy in cell E5 In cell F5 I would like the result of the number of days between E5-A5 (Excluding weekends) In cell G5 I would like the total number of days in the range F5:F25 In cell H5 I would like the total number of working days from A5 to Today I am using the following formula to get the result in F5 etc =SUM(INT((WEEKDAY(A5-{2,3,4,5,6})+E5-A5)/7)) I have copied this down and in the blank cells I get #NUM! In cell F? were there is no date entered in cell E? I get a minus number (which I can hide using conditional formatting), but the minus number obviously has an affect on the result in G5, as cell G5 gives me #NUM! instead of the result for the current numbers in row F
View Replies!
View Related
Calculate Work Days Between 2 Dates
i have a few fields with dates in my excel sheet. I also have a field, where i want to automatically insert the business day between two other date-fields! Business date means days without the weekend and public holiday! Is it possible to do that? how?
View Replies!
View Related
Calculate The Time Between Two Points (on Different Days)
I am trying to calculate the duration between two times on different days. For example - I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00. I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.
View Replies!
View Related
Calculate Working Days Function
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
View Replies!
View Related
Formula To Calculate Sick Days
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell. For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
View Replies!
View Related
Calculate Days Between Subtotaled List
In the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number. I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.
View Replies!
View Related
Calculate Days Between A String Of Dates
I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.
View Replies!
View Related
Calculate Times That Span 2 Days
I have formated my cells as custom hh:mm and want to have a 24 hour clock calculate time. In cell D6 I have starting time of 23:00 In cell E6 I have finish time of 07:00 This should equal 7:00 hours but I get ######### am I doing somthing wrong or do I have to format the cells differently. The formula in F6 is E6-D6.
View Replies!
View Related
Calculate Number Of Days Using 2 Sets Of Dates
I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot. Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range. For example: In A3 type "1/1/2006" In B3 type "1/1/2007" In C1 type "6/30/2005" In C2 type "6/30/2006" Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/2006-1/1/2007 fall in the date range of 6/30/2005-6/30/2006. Basically for this is should be half the year, however many days that is. Is this possible with a formula?
View Replies!
View Related
Calculate The Number Of Days From Start To End
I have 2 dates a Start Date and an End Date. I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column. Start End Yr1 Yr2 Yr3 11/01/99 11/01/00 11/01/01 10/31/00 10/31/01 10/31/02 Col A Col B Col C Col D Col E 10/31/00 07/05/01 365 117 0 I thought a series of IF statements but I am open to an easy solution...
View Replies!
View Related
Formula To Calculate Years Months And Days
I am using the following formula to calculate years months and days in Excell 2007 =DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days" C7 3/24/04 D7 1/4/08 What is returned is 3 years, 9 months, 136 days How might if fix it show the correct days?
View Replies!
View Related
Calculate Working Days Left In Current Month
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule. We are open M-F and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically. On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month. Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08-20-2007). Is there a way to get Excel to do that?
View Replies!
View Related
Formula Required To Calculate Number Of Days Remaining
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively. A1 B1 C1 D4 2 4 6 C1-Formula is: =sum(A1+B1) Now I have to write formula for D4-cell: i.e. =sum(C1/Total days remaining of the month) Suppose Today is April 01, 2009, C1/should be devided by 30, Tomorrow will be April 02, 2009, C1/should be devided by 29.
View Replies!
View Related
Formula- Calculate The Number Of Days Since An Employee Worked
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View Replies!
View Related
Calculate The Number Of Days Between Two Dates Using The Days360 Function
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.
View Replies!
View Related
Calculate Day Difference Between Dates Excluding Certain Days
I am attempting to get the formula in Column "E" (see attached excel file) to work based off the individual reps 'days off / holidays' (Columns K:P (or more as needed) that they might have. the formula I am using (which works fine) is: =IF(C2=D2,0, SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&D2)),2)={2,3,4,5,6,7})*(COUNTIF($K$2:$S$2,ROW(INDIRECT(C2&":"&D2)))=0))) My issue comes up when I am coping this formula for say 100+ rows. When I copy the formula to all of the rows, the (COUNTIF($K$2:$S$2) becomes static and does not realize that the row (ie. Row 26 Rep C) has different days off than Rep A. I can manually change the reference for the countif to specify the correct row to their own days but that becomes tedious very quickly. I was wondering if anyone has come across a way to make the countif work based off of that the name in Column B matches the name in Column G then looks 'to the right' and uses the 'days off' that are listed for the corresponding Rep. I have tried a few different ideas but nothing has worked so far.
View Replies!
View Related
Conditional Count (calculate Number Of State Holiday Days)
I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours. I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on. specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file. if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.
View Replies!
View Related
Calculate Employee Vacation Accrual Rate & Number Of Days Taken/Left
Our office has a vacation days accrual policy based on number of years worked. We have a vacation days number, based on year of employment, the employee earns monthly. I need help with a worksheet, formulas, to document each employee, the year of employment they're in, # of days they have available based on the current month (which would need to add up automatically as the year progresses), then any days they request off, and finally a remaining balance of days left.
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
If Date Cell Less Than Todays Date Calculate Difference In Days
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"
View Replies!
View Related
Paid Vs Unpaid
I have two excel sheets. The first lists all orders submitted. The second lists all orders paid, or pending payment. I would like a column in the first sheet that would show if that order was paid, by pulling the info from the second sheet.
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
Sum By Date Paid
I am trying to sum rows based on unusual criteria: i.e. A table of date c2:t9 contains details of expenditure for 16 periods for insurance and motor expenses. i want to convert this to a cashflow format and reflect when the item is actually paid, so if it is paid in february, march, etc (given in the original table) I want to add the totals in the months preceding the set date. also, if the criteria is a number <12 then i want to add up the preceding amounts if the period number is a factor of that number e.g - criteria is 3, then i want to add rows 1-3 and put in period 3, 4-6 and put in period 6.
View Replies!
View Related
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
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
|
TRACKER
