Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
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
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
Formula To Calculate Sales Tax From Total Sales
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and filling in the chart on excel but I would like to create a formula that automatically does it for me based on total sales.
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 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 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
Capping Amount In Formula?
is there a way to create a formula that caps at a certain amount? i am trying to create a spreadsheet for a group of doctors and their insurance coverage. i want to display that a certain amount of coverage caps at a certain price over the course of a number of years. so basically, can i create a formula that automatically adds the data and max's out at a certain number?
View Replies!
View Related
Formula To Zerorize The Amount That Is Within The Threshold Value
my data will look like table below: amount (A1) threshold(10) (B1) threshold(15) (C1) threshold(25) (D1) 9.80 0.00 0.00 0.00 0.26 0.00 0.00 0.00 0.53 0.53 0.00 0.00 0.40 0.40 0.00 0.00 0.77 0.77 0.00 0.00 1.20 1.20 0.00 0.00 0.33 0.33 0.00 0.00 3.40 3.40 0.00 0.00 5.67 5.67 5.67 0.00 4.00 4.00 4.00 0.00 1.20 1.20 1.20 1.20 3.10 3.10 3.10 3.10 0.53 0.53 0.53 0.53 1.73 1.73 1.73 1.73 i need a formula to zerorize the amount that is within the threshold value & just display the same amount as input if exceed. column A is my input, colum B,C,D is my working area where there are few threshold value. for example, the B column give the threshold value = 10, thus cell B2 & B3 were zerorize. ( not B2 only)
View Replies!
View Related
Delete The Formula In The Amount The Unite Price Comes #value
(To keep things simple from left to right Column A-H) The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed. I have a vlookup formula in Column F (thank you VoG) =IF(ISNA(VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)),"",VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)) To pull prices from another worksheet.
View Replies!
View Related
Formula To Subtract Invoices From Total Amount Available
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
View Replies!
View Related
Formula To Add Amount Of Rows In A Column
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column. Does that make sense?? ok NAME rob dave jim darren adam lee i need a formula that will tell me there is 6 rows used in that column (not including the title).
View Replies!
View Related
Count Formula One Record With Amount Is E, F, &/or G
Need count formula to count records with amounts in either columns E, F, or G. For example Need a formula (not VBA) in cell F2 to return a count of 5 records counted that have an amount in column E, F, or G (but only count as one record when amounts exists in multiple columns): __|____E___|____F___|___G___ _7 | 1200.62 | 1500.53 | -0- _8 | 1000.00 |________|_1620.00 _9 | 7000.00 |________|________ 10 |________|________|________ 10 | 2000.00 | 3000.00|________ 11 | 8000.00 |________|________
View Replies!
View Related
Formula To Put An Amount Into A Cell Next To Only Unique Item
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet. I can get a grand total by summing the unique items by using this formula: =SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970))) and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take." However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
View Replies!
View Related
Formula That Calculates The Total Rent Amount For Particular Dates
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
View Replies!
View Related
Summation Across Of A Variable Amount Of Columns VBA Formula
I'm trying to enter the summation formula into three columns that vary in placement based on the value of num_objs. Basically, the number of grey columns in each of the three sets will equal num_objs. I want the user to be able to enter values into the grey columns in the middle section and the values of the two rightmost white columns (with '0's in them currently) will change accordingly. My current code is below. However, I keep getting a compile error saying "Expected: end of statement". I've been searching for a solution online for a couple days now, and I don't see how my code is different than what I've been seeing of other people using variables in their formulas.
View Replies!
View Related
Formula Doesn't Provide Amount As Expected
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
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
Formula's For Calculate Sales Commissions
I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %. AgentSales Price CostComm Pd AS150 75 JK255 185 JD325 250 JD125 50 AS50 10 AS50 10 AS335 250 JW75 25
View Replies!
View Related
Sumif; Find The Amount Spent Based A A "tag"
I have a spreadsheet(see attached) that I am needing to find the amount spent based a a "tag" I have created. If you bring up the spreadsheet and look at the dashboard sheet, I am wanting to find the total tag amount based off what is listed in the paychesk sheet. For tests I have used this formula and have put it in B6 on the dashboard sheet. =SUMIF('PayCheck - DEC-09-B'!$F$2:$F$1000,A20,'PayCheck - DEC-09-B'!F$2:$G$1000) But the "bank amount" field on the paycheck sheet is a negative number the value of course coming back is not right. How do I fix this?
View Replies!
View Related
Countif Formula: Added The Correct Amount In The Correct Cells
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View Replies!
View Related
Calculate To Percentage Reached, Then Start Over
I have a column that is a percentage(p%) which measures the fullness of the location(loc) that the item(ite) is in, which also has a corresponding location(loc) and item column(ite). What I need to do is add as many items(ite) to one location(loc) as possible without exceeding 100%(p%), however if the value exceeds 100%(p%) then we need to start all over in a new location. The end result would be an unknown amount of unused locations. If p% < 100 then add the next row's p% value, else start over with a new location. I am trying to add as many items to one location as possible, and once a location fills up, we will use the next one.
View Replies!
View Related
Dollar Amount
How can I display the $xx.xx into a statement? I have these $ amounts in column A: 99.12 107.65 14500.99 Have the result in column B as: Please Pay Exactly$00000xx.yy (where xx is tenth, hundreds, thousands and yy is the cent) 00000 is to prevent forgery and to have the results in column C using same column A: Please Pay Exactly$0000xxDollars and yycents For example like Please Pay Exactly$000099Dollars and 12cents or Please Pay Exactly$000014500Dollars and 99cents
View Replies!
View Related
Replacing $ Amount With X
I need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View Replies!
View Related
Calculation A Total Amount
I am trying to write a calc for a total an amount based on a list choice Here is the Set up Fields A1 thru A255 are number 1 to 255 Fields B1 thru B255 are number 255 to 1 Field C1 is a List box set using A1 to A255 and its list Field D1 is set as the needed Calculation When you choose a number in C1 the D1 calculation should total the amount of Fields B* to B1, * being the corrisponding B field to the A field from the list Example C1=100 (Field A100) D1=Sum of Fields B100 to B1
View Replies!
View Related
Amount To Words Conversion
I am using excel for invoice making...In the end i have a total amount. I need to type it in words for every invoice at present. How can i convert it to words automatically? Is there any formula to it? Or is there anything else? Please suggest me friends.
View Replies!
View Related
Rasterize Displayed Amount
I have a column with dates in the following format: dd/mm/yyyy h:mm but im doing an XML export and excel only exports in text format. I would like to somehow resterize or only use the displayed amount instead of the text which is exported (ie- 37805.1640277778)
View Replies!
View Related
Subtract X Amount Every Y Years
I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.
View Replies!
View Related
Dollar Amount Inventory
I have a total inventoried product dollar amount in a cell. I have 4 other cells with different dollar amounts in each for product I inventory out! In the seperate balance column, as I use a letter ...
View Replies!
View Related
Add Amount In Column
Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march. The format for the date is 3-12-09. Here is what I'm trying. =SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
View Replies!
View Related
|
TRACKER
