Commission Calculator
Attached is sample data. Target payout is the amount of the base salary a salesman can earn if all goals are reached. Commissions are payed quarterly. Listed on the left are 7 goals, each with a weight of importance toward the target payout amount. The percentage achieved can only be 100% or 0%, nowhere in the middle, although they are allowed to make up a goal later if they do not make it at first. This is where I get stumped. For example, if they don't make a goal in quarter one but make the goal in quarter two plus what they missed in one, they get two quarters worth of commission in quarter 2 for that goal. I am having trouble figuring out the easiest way to go about this.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Commission Formula
I am trying to write a formula for my commissions spreadsheet, which calculates commission clawbacks based on a sliding scale. From my understanding I need a code that will calculate additions or deductions based on a range of probabilities. For example, if I have a percentage figure that is below 8%, I would like to add 15% to the total commission earned. Here are the ranges below: 8% or under+15% 813%+ 10% 1317%0 1722%10% 22% or more15% If I say my % is in K5 and the monetary value is in I5, what formula would I type into L5 to calculate the amendment?
View Replies!
View Related
Calculating Commission
Sub setcommission() Sheet3.Select Dim cellsNum As Integer Dim commission As Single, rating As String Dim sales As Single Range("b2").Select cellsNum = ActiveCell. CurrentRegion.Row.Count For i = 1 To cellsNum If ActiveCell.Value > 150000 Then commission = ActiveCell.Value * 0.012 rating = "superior" Else sales > 100000 And sales <150000 Then commission = ActiveCell.Value * 0.08 rating = "satisfactory" Else I am trying to calculate the commission for the sales for sales>150000 a commision charge of 0.012 and rating of superior. for 150000> sales >100000 a commision charge of 0.08 and rating of satisfactory. for other sales a commision charge of 0.04 and rating of unsuperior. i am finding a few problems with the code 1st a problem with 'cellsNum = ActiveCell.CurrentRegion.Row.Count' it says complie error: invalid qualifier 2nd a problem with 'Else sales > 100000 and sales <150000 then'
View Replies!
View Related
Calculate Conditional Commission
The following is assumed. I have a base salary of $5k/monthMy PROFIT on hardware sales must equal or exceed my monthly salary in order for me to earn a commission on hardware sales. If it does, my commission is then Profit beyond monthly salary X 25%I also earn commission on software sales. Total Commission is the sum of Software commission plus commission on hardware sales. Example: HW Profit is $6k. Software commission is $3k. $6k  $5k = $1k. $1k * 25% = $250. $250 + $3k = $3250 However, there are times (unfortunately) when my total hardware profit does not equal or exceed my monthly salary. When that happens, I am now in a negative status regarding hardware commission. The negative status is simply salary minus hardware profit. Total commission is then that negative number plus software commission. Example: HW Profit is $3k. Software commission is $3k. $5k  $3k = $2k. $2k + $3k = $1k I need a formula in one cell to account for both possibilities.
View Replies!
View Related
Sales Commission Calculation
I need to know what formulas to put into the cells in excel to make the following sales compensation example compute properly: Time Period Draw Paid Actual Commissions Owed to Company Commission Paid Total Earnings Month 1$3,000$4,000$1,000$4,000Month 2 $3,000$2,000$1,000$3,000Month 3 $3,000$5,000($1,000)$1,000$4,000TOTALS$9,000$11,000$2,000$11,000
View Replies!
View Related
Calculate Bracket Commission
I have been thinking about this for a few days and have no idea where to start. The commission scheme pays like this: upto $40,000 in sales pays 30% $40,001 to $80,000 pays 40% $80,001 + pays 50% Also, the sales person will only earn commission once they have invoiced 1/3 of their basic salary. Example $60,000 must invoice $20,000 per month, therefore commission is actually 30% of the remaining $20,000. I want to create a spreadsheet that allows me to enter the basic salary for individual sales persons and their individual sales figures to calculate their gross commission and also their gross basic salary if I can.
View Replies!
View Related
Computing Commission Formula
determining a formula to compute a sales commission. Here is a sales scenario. A $25 commission will be paid on sales between $50 to $150. A $50 commission will be paid on sales between $151 to $300 A $75 commission will be paid on sales between $301 to $600 The sales person will enter the sale amount into column B. Column C should compute the total commission for multilple sales. Example: Column A Column B Sales Commission $50 $150 (which is the comm. for the combined sales) $175 $360
View Replies!
View Related
Rewrite Commission Spreadsheet With A New Structure
I'm trying to rewrite our commission spreadsheet with a new structure and as my excel knowledge is limited, I'm not really getting anywhere. I'm looking for a few lines of formula however I'll just post one at a time otherwise I'm in danger of scaring you all off! Firstly, I am trying to work out the formula for the following: If the value in C7 is up to £14,999 = 1.5% of the whole value is given, if it's over £15,000 = 3.5% of whole value is given. I would like the total amount of commission to show in F7.
View Replies!
View Related
Multiple Variables In A Commission Structure
I'm trying to create a worksheet to calculate ourcommsiion structure, but can't figure out a way to attack it. We have manyvariables (5) in our commission structure based on each order. Here's how I set it up so far: (In Cloumns) A= Order Amount B= "Y" is A15%; "N"=A C= "Y" is B*20%; "N" is B*10% D= "Y" is B+2%; "N" is B E= "Y" is B+2%; "N" is B F="Y" is B+1%; "N" is B G= SUM(A:F) For example, if the order is $1000, and I answer y,y,y,y,y=$212.5 How do I create the formulas so I can just put in the order amount and the appropriate letter to get the correct commission structure?
View Replies!
View Related
Formula/Function For Commission Calculation
I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for.. $0  $10,000  60% commission $10,001  $15,000  65% commission $15,001+  70% commission.. i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated.. ps.. my excel sheet is set up as follows: Rows ag (stuff that is irrelivant) row h, gross fees row i, commission (in dollars)
View Replies!
View Related
Weekly Average Commission Calculation
I hope this question has not been addressed but the closest I can find is in this link: [url] I am now a commission based contractor who started earlier this year & I want to be able to calculate my current average weekly income which should fluctuate greatly. I have a spreadsheet that works out what my current to date net income is but can't figure out how to break this down to a weekly avarage. My basic guess is that I want to take the figure provided and divide it by the number of weeks from "stated start date" to the current date (but on a divided by 7 basis?) to get my average weekly net income. I am sure this is pretty simple for you all so I hope I am not wasting time it's just that if I try figure it out I am using something like WEEKNUM & that will fail after the next new year. Although it would be better to base it all around the financial year if that can be done? (March 1st  April 31st in NZ)
View Replies!
View Related
Real Estate Commission Schedule
I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7. My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned $8137, my percentages for sales all jump to Row 3. I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.
View Replies!
View Related
Sales Commission Formula Required
I have a new sale structure to put in place the commission is paid in the following way: below 1500 zero commission between 1501 and 3000, commission at 16% between 3001 and 8000, commission at 23% above 8001, commission paid at 30% Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! ) I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2  {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
View Replies!
View Related
Nested IF Formula: Calculate The Commission For My Employees
I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example: If Profit is between $1.00 and $2.00  commission = 15% If Profit is between $2.01 and $4.00  commission = 20% If Profit is between $4.01 and $6.00  commission = 25% If Profit is > than $6.00 then  commission = 30% I am able to calculate the first level ex: =IF((C3B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?
View Replies!
View Related
Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )
I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet. Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up. The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.
View Replies!
View Related
Age Based Calculator
I am trying to put together a spreadsheet that works out values based on a persons age or service length. So in I have the following data in columns starting at B4 and ending at k4: Date of birth; start date; text; text; text; text; Weekly Pay; Start age; Current Age; Years Service. The formula I need to provide in l4, m4 and n4 comes from the following rules: * 0.5 week's pay for each full year of service where age during year less than 22 * 1.0 week's pay for each full year of service where age during year is 22 or above, but less than 41 * 1.5 weeks' pay for each full year of service where age during year is 41+ So in l4 I have: =IF(J4
View Replies!
View Related
Birthday Calculator
I am hopeless at remembering birthdays tbh  so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday. Please see attached  I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old  but '=TODAY()C16' yields "00 Years 4 Month(s) 12 Days" Also, 'Leah' has just her birthday  but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())YEAR(C4))*12),"d")'
View Replies!
View Related
Freight Calculator
I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself. Here's my conundrum: A potential customer enters "80802" for zip code and "Solomon" for store. StoreLocation_________ City_______ State_____ Zip______ Distance Solomon__________ Arapahoe _______CO ____ 80802_____ 270 Garden City_______ Arapahoe_______ CO _____80802_____ 143 The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.
View Replies!
View Related
Golf Calculator
Not sure if this is doable but I figured I would try. For a golf league coming up later this year I want to figure out how many Birdies, Pars, Bogies, Dbl. Bogies and Others each (20) golfers have during the season. After each round I would input their scores and I am looking for a program that would look at the score of the hole and the par for the hole and figure out what they got 1 under par = birdie, even par = par, 1 over = bogie, 2 over = dbl bogie and 3 over = other. I started by creating a simple if statement but it ran out too long and my other issue is adding up the number of birdies, pars..etc for each round. Meaning a golfer can have bogies on hole #1 and #2 and the if statement can take care of that but how would get a total saying the golfer had 2 bogies. Something like this with the  meaning a column. birdies Pars bogies dbl bogies others total holes 4  5  3  4  4  5  6  5  8 1 4 2 1 1 9 I would do this each week and total the number of each to keep a running total at the bottom of each column. We play on the same course each week so the pars for each hole can be hard coded.
View Replies!
View Related
Date Calculator
I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list: 10 days from start date is: 15 days from start date is: 20 days from start date is: and so on... I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.
View Replies!
View Related
Absence Calculator
im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 25/12/07). I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work. Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence). How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months). I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.
View Replies!
View Related
Get Rid Of The Calculator Toolbar
I downloaded the calculator toolbar, and for some reason it doesn't look right, it doesn't resemble a calculator layout at all. The numbers are skewed, as though the layout is in landscape view instead of portrate. I've tried repeately to delete it from the toolbars menu option, with no success. It's become annoying.
View Replies!
View Related
Billing Calculator
I'm working on a 4week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate. So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined. Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be.
View Replies!
View Related
Timesheet Calculator
See workbook attached. I'm looking for help to detemine rates so it automates in the sheet. Can you give me assistance and code perhaps ? I'm pretty basic at VLookup and If functions. Is this the best route to take ? All is explained within the workbook.
View Replies!
View Related
Little Calculator Wanted
I've been trying but excel does not seem to recognise x and y so here it is 2* X = Z Where 2 is changable X = Z2 Now i just want to find what Z is. Here's an Example 1.3x  z 1.3(x.06) = z 1.3z  .078 = z 1.3z = z + .78 1.3zz = .78 .3z = .78 z = .78/.3 z = 2.6
View Replies!
View Related
Progress Tax Calculator
I'm trying to come up with an efficient formulae or function to calculate tax The problem I have is that the tax is progressive. As below the first 20,000 is taxed at 5% the next 20,000 is taxed at 6% the next 20,000 is taxed at 7% the next 20,000 is taxed at 8% the next 20,000 is taxed at 9% more than 100,000 is taxed at 10% I'm trying to do a formula like below Cell B3 is my taxable amount Cell B5 =IF(B$3>20000,20000*0.05,B$3*0.05) Cell B6 =IF(B$3>40000,40000*0.05,(B$320000)*0.05) This gives me a problem in that for 35k say, I end up with a negative number for the second part in cell B6. My other issue is that each calculation will take up 6 rows on my spreadsheet. I was hoping to set up a function that could do this in a cell, but even the simple stage defeats me at the moment.
View Replies!
View Related
Staffing Calculator
I've been trying to create a Staffing calculator for a call center. Basically the calculator should be able to add up the number of agents for the next 18 intervals based on the login time that is entered by the user. I've just outlined the functioning below: Suppose 10 agents login at 8 AM (thus logout at 5 PM), the intervals right from 8 AM till 5 PM should show up the 10 agents. Now suppose 10 more agents login at 9 AM, we would then have 20 agents logged in till 5 PM (since agents logged in at 8 will logout at 5) & the remaining 10 till 6 PM. Thus if we have 10 more agents logging in at 10:30, we will have 30 agents till 5 PM, 20 till 6 PM & 10 agents till 7:30 PM & so on. I have attached an excel file to explain the example & the way the calculator has to be built. It is preferrable that the cells containing the login time aren't fixed, but the user should be able to input any login time in any cell.
View Replies!
View Related
Build A Working Day Calculator
I need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only. I need to imput the date the invoice is received and then for the rest to be worked out automaticly I need it to do the follwoing ....
View Replies!
View Related
Night Payment Calculator
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out. Essentially it's for calculating night payments for our employee timesheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use. To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00  06:00 where they qualify for night payments but also from 24:00  30:00 if that makes sense?
View Replies!
View Related
CommandButton, Text Box, Calculator
I am trying to make a calculator inside of Excel...learning how Text Boxes work in conjunction with CommandButton. I am trying to code the button to display inside of the text box. I have never coded a text box before to do anything like that note: For right now i am just looking at being able to click on a button [numbered 09] and have them dsplayed inside the text box. After that I want to be able to set up an addition, subtraction, etc button to actually have the math done.
View Replies!
View Related
IF/AND Statements In Comp Calculator
Almost done with this but I'm stumped on the last remaining formula. The way this compensation plan will work is if reps are between 025% growth they will get paid $100 per point, 2650% $150 per point, 5175% $200 per point. Growth is calculated monthly vs. a monthly #. My problem is that if the rep is at 40% they get the first 25% at $100 and the next 15% at $150. My formula is an either or thing. The following are my formulas for 025%, 2650%,5175% respectively: =IF(AND(D9/C9>0,D9/C9<1.25),((D9/C91))*100)/(B9/52) =IF(AND(D9/C9>1.26,D9/C9<1.5),((D9/C91))*150)/(B9/52) =IF(AND(D9/C9>1.51,D9/C9<1.75),((D9/C91))*200)/(B9/52)
View Replies!
View Related
Quote To Sale Calculator
I work in sales and when under target I need to know how much more i need to sell to hit target. e.g quotes 10 sold 1 currently at 10% my target is 50% at the minute i would have to work this out manually like i need to quote and sell the next 8 to hit the 50% target. What can i use on excel putting my current quotes and sales and my target in so it gives me the quotes and sales i need to do?
View Replies!
View Related
Fee Calculator Sheet Formatting
I am designing a basic front end using formulas as the work computers dont like VBA script. I need the calculator ( attached) to replicate the 'calculations' sheet based on the number of accounts specified in 'Investor Details'. Ideally, I would like to use a button or similar to make it user friendly. I also need to be able to hide rows based on a condition being met and conditional formatting doesn't seem to work (eg. 'Investor Details' 1014 if C8 = Yes.)
View Replies!
View Related
Create A Football Calculator
i m just trying to create a football calculator, just a real basic one, my excel is ok. Just doin it for a mate as he is arranging some charity football event. i have inputted the teams and that and worked out goal differences but would like to calculate games played, wins losses draws and points, can someone advise or quicky have a look, see attachment.
View Replies!
View Related
Bank Interest Calculator
I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)... Public Function BankCalculator() 'Bank Calculator for different accounts 'declare variable Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double Set shtBank = Application.Workbooks("Bank Calculator.xls") 'input box for amount of money, assign address intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................
View Replies!
View Related
UK Bank Holiday Calculator
I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls  I didn't know it could be worked out, but it can!! I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.
View Replies!
View Related
Simple Dialog Box Calculator With Insert
I am interested to know how to produce a dialog box that pops up upon a button press that does a simple calculation principal plus interest calculation.. ie. the msg box or userform when called would have the following: Label1 (Principal Amount) ===> Textbox1 Label2 (Interest rate per year) ====> Textbox2 (formatted to two decimal places). Label3 (Results shown) ====>Textbox3 than an "ok" button on form or "submit" button when submitted, it is entered into a defined cell.. Just as an aside or complication, is it possible to have say after Label3 , 2 dropdown box where one reads colum values and one reads row values on the worksheet, that one people can pinpoint where to enter the final value from Label3
View Replies!
View Related
Manufacturing To Retail Cost Calculator
I am trying to make an EASY Manufacturing Cost to Retail pricing calculator. This calculator would have ability to include cost of goods, labor, markup etc of components manufactured and sold as retail products, example: small bookshelves versus large bookshelves or cabinets all have different materials (wood types, stain etc) in determining the final retail product costs that would reflect time of labor hours involved in producing to determine final retail costs. Should be simple in Excel 2000 (my version). I have the basic template created and have used Data Validation Drop Down Lists and utilized LOOKUP function. While my knowledge is limited in Excel, I am frustrated as how the LOOKUP function works, I can only get it to work where in the formula, the costs per unit are input manually, whereas I would prefer to have the data input automatically from columns of calculated wholesale cost plus markup per square foot data, ie: =lookup(A1, X1:X30, Y1:Y30) but get errors when doing this. Instead this works: =lookup(A1,{"pine", "oak", "birch"},{"3.99","7.87","5.15"}) and using this cell (A1) in my calculations for including the square foot costs in the final retail calculations. While this seems to work, it is not easily modifiable as costs change rapidly and would like to easily input the cost per square foot of the different woods in their own cells rather than in the formula calculation of the lookup. Hope that makes sense..... any suggestions? perhaps lookup is not the best function for what I am attempting?  sample file is attached, light green are notes of where my data is located.
View Replies!
View Related
Body Mass Index Calculator
I'm trying to make a BMI (Body Mass Index) calculator. I've attempted it already but to no success. My first mistake was to read the values from cells in the spreadsheet which made things more complex than needed. So I started a new macro using a form instead for all data entry. I'm running into multiple problems with this Ask the user which method they prefer (see below formulas). Input the result into a certain cell. English BMI Formula BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703 or Metric BMI Formula BMI = ( Weight in Kilograms / ( Height in Meters ) x ( Height in Meters ) )
View Replies!
View Related
Property Management Availability Calculator
I am working on a Property Management spreadsheet to track the availability of vacant units (and upcoming vacant units). I'm trying to write a formula to count the number of current units that are listed as vacant, so that I can ultimately report on the overall unit availability as of any given period (today, 15, 30, 60, 90days out). All of the data in this spreadsheet is dynamic and each field can change at any time. There are also a lot of blank fields as well. I am using Excel 2003. Here are the fields/data that I am using for my analysis: Column A  (Reporting Period Dates): A2 = 5/20/08, A3 = 6/5/08, A4 = 6/20/08, etc. Column B  (Unit Status: V=Vacant, O=Occupied): B2 = V, B3 = V, B4 = V, B5 = V, B6 = V, B7 = V, B8 = O, B9 = O, B10 = O, B11 = O, B12 = O, B13 = O Column C  (M/O Date. These dates represent when the current resident will move out)  Cells C2:C13 contain dates for when each tenant will move out Column D  (M/I Date. These dates represent when the future resident will move in)  Cells D2:D13 contain dates when the future tenant will move in If my reporting period is 5/20/08 (cell A2), then I need to calculate how many Vacant units (B2:B13) I will have as of 5/20. The formula will need to count all of the current Vacant units (B2:B13), plus it will also have to take into account any Move Outs (C2:C13) during the reporting period. For example, let's say we have an Occupied unit that is scheduled to move out on 5/15/08. The cell will show this as "O" but the formula will need to determine if the M/O date is <= the Reporting Date of 5/20/08 (A2). If so, then Excel would count this unit as a Vacant for the period. In addition to this, I also need the formula to look at upcoming Move IN dates (D2:D13). The formula will also have to determine if the MI date is <= the Reporting date (A2). If the MI date falls prior to the Reporting Date, then this unit should be considered as Occupied and should NOT be added to the total available units. NOTE  there are quite a few cells in column D that are blank and don't have MI dates. If there isn't a MI date, then the formula should assume that those units are Vacant if it is past the prior tenants MO date. I'm assuming that Excel can handle multiple conditions like this, however, I am not smart enough to figure it out.
View Replies!
View Related
Time Code Calculator 30fps  Mac
I found this great formula posted on this site for calculating time code @ 24fps: =TEXT(LEFT(B2,8)LEFT(A2,8)(RIGHT(A2,2)>RIGHT(B2,2))/86400,"m:ss")&TEXT(MOD(RIGHT(B2,2)RIGHT(A2,2),24),":00") Can anyone tell me how to change it for 30fps? I'm using Excel 2008 for Mac, so I'd rather use a formula than figure how to make VBA work on Macs.
View Replies!
View Related
Creating A GUI Front End For An Elaborate Calculator
I have a very elaborate excel file that will calculate a price for our customized product. The user enters figures into about 15 fields, and hundreds of calculations take place and give the user a unitprice for that item. Further, this excel workbook contains 3 queries to our enterprise database that get updated on opening. What I want is to have a frontend GUI form that the user accesses like an html page that can be viewed internally by only users withing the intranet. The main point here is that I have very sensitive information in the workbook. The last thing I want is for it to be emailed to our competition. Protection is not an option either, because I heard it can be easily cracked. I want my user to access the excel spreadsheet without ever getting their hands on the original. Maybe this thing should have been written in some other software package all along, but this is what I have to deal with.
View Replies!
View Related
Calculator Formula For Addition Via Columns
I would like to know the calculator formula for addition via columns. Eg 1. If i were to place 135 into Column A ; 12.95 into Column C ; i would need to yield a result of 147.95 Eg 2. Place 189 into Column A ; 12.95 into Column C i would need to yield result of 201.95 and so on. in the attachment is the sample file.
View Replies!
View Related
Meter Reading Calculator Based On Date
As part of a project, I need to come up with a meter reading calculator. I start of with 3 base meter readings (A2, F2, K2), which would be read from the meter. 3 reads are needed in case the consumption changes dramatically at some point. I then need Excel to work out the reading for dates in between. The formula needs to be dynamic enough so that I can change any of the grey dates to what I want. The estimates need to be calculated using an average daily consumption. This figure needs to be displayed in D9. It would be preferable if macros weren't used. If its possible to explain what to do without an attachment, that would be good too.
View Replies!
View Related
