Formula That Calculates The Total Rent Amount For Particular Dates

Oct 5, 2009

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.

Formula That Calculates A Total Numbers

Oct 3, 2008

Column C contains zip codes.
Column D contains # of adults in the household.
Column E contains # of children in the household.

How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?

Total Yearly Rent Calc, With Random Adjustments

Aug 6, 2006

i have a problem that i have been trying to get over for about a week now.
i need to calculate a lease commission, with an extensive amount of variables.
first i need to find the length of the total term which should be anywhere between 1 to 10 years.
then on a annual basis i need to define how many months are billable in that year.
which gives me to variables to account for there, which are
A= initial free months, non paying
B = the last month of last year may only be a half year

i think i have worked that out pretty successfully, so next i need to calculate the rent for each year period. with several variables
a= the rent can be caculated :
-by per month basis
- by annual basis
- by a per square foot basis
b= next in relation to annual rent operating expenses may also be calculated in the annual rent number also by the same variables, however it may or not be calcuated into the number depending on the lease.

c. this is where i am at now, and its killing me. i need to account for rent adjustments for each year.
rent adjustments can start from either the lease start date or the date that rent starts which would be after the lease start if free rent is granted.
then the adjustments will continue through the end of the term and be implimented every x number of months.
the value of the adjustments will either be a percentage of the first years rent usually 3-5 %
or per sf, per month, or just flat rate per year. but it will escalate each year.
for example year 5 is x amount of ajustment from year 4.

i am finding difficulty in finding an annual value of the original lease term in relation to this date series. expecially if the adjustment periods leave a remainder carring over to the next year, or if their are several adjustments in one given year.
any help would be appriciated on this.... i know its pretty complicated, and i have rewritten this code about 30 different ways , i am at a loss right now.
if you think you may want to see my file let me know and i can post it

Formula To Subtract Invoices From Total Amount Available

Sep 25, 2009

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.....

IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)

Feb 1, 2009

I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.

Excel 2003 :: Set A Formula To Auto Calculate The Staggered Rent For The Month?

Jun 17, 2014

set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.

Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)

formula or vba using Excel 2003.

Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

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.

How To Total Amount By Name

Oct 17, 2011

I want to sum income of my team by name without sort.

Example: I have as beloow in excel:

\$ 10.91 Khear
\$ 10.91 Khear
\$ 10.91 Vichda \$ 22.73 Linda \$ 31.82 Linda \$ 13.64 Vichda \$ 10.91 Khear
\$ 10.91 Khear
\$ 10.91 Linda
\$ 13.64 Virak \$ 10.91 Khear

So I want to calculate name Linda, how much? Khear How much? without sort or separate for calculate.

I Need A Formula That Calculates Sum Of Row Widths

Oct 27, 2007

I need a formula to calculate the sum of a range of rows.

Example:

If rows 1-10 have a width of 12.75 each, and rows 11-15 have a width of 15.5 each. I need a formula to tell me that the sum of the widths of rows 1 to 15 is 205.

Any idea?

Calculation A Total Amount

Aug 14, 2008

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

Sum By Name & Hours For Total Amount

Mar 7, 2008

I have pivot tables where I need to look through a series of names and sum the hours and dollars charged by that person. When I try to do a pivot I can get it to read

column 1 Column 2
John Doe \$4000
Mary Smith \$ 500

But if I try to add the hour columns it breaks it out by the week the hours was charged

column 1 Column 2 Column 3
John Doe \$2000 4
2000 4
Mary Smith \$ 250 5
250 5

I want it to just give me one dollar total and one hour total per employee.

Modify Formula That Calculates Time?

Jun 6, 2014

I have the following formula:

=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")

It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?

RC[-1] Formula: Calculates Monthly Residuals

Nov 20, 2008

I'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of \$275 in the first month and then the next month I have the first \$275 and an additional \$275, and so on. I've created the following formula

=RC[-1]+(RC[-1]*2)+(RC[-1]*3)+(RC[-1]*4)+(RC[-1]*5)+(RC[-1]*6)+(RC[-1]*7)+(RC[-1]*8)+(RC[-1]*9)+(RC[-1]*10)+(RC[-1]*11)+(RC[-1]*12)

where RC[-1] is the base amount that adds on every month. This gives me the number I am looking for. However, I am wondering if there is an easier way to do this.

Formula Calculates Correct Value But Does Not Display

Mar 18, 2008

Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:

Example 1 - =COUNTIF(Data!M\$6:M\$9871,3)/\$E\$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!

Example 2 - =SUM(IF(Data!\$J\$6:\$J\$9871=\$D8,IF(Data!\$M\$6:\$M\$9871=3,1,0)))/\$E\$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!

Excel 2013 :: Formula Disappears Once It Calculates?

Jul 17, 2013

using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?

Formula: Calculates % Of Budget Based On Signs

Feb 5, 2007

I am having an issue creating a formula that calculates % of Budget based on signs. What I mean is positive budget, negative actual and vice versa. Here are all the cases. Can someone please create a formula that I can copy down.

Actual Plan% Budget
85,60696,85188.4%
70-82185.9%
-1,530-3,786247%
-100100-200%

Inserting A Total Amount Field In Each Empty Row

Apr 21, 2009

to run a macro that enters a new row after each set of currencies (which have been sorted). I now need to enter a total balance cell within this empty row which calculates the total amount for each currency.

I need to do this using the SUMIF function below:
SUMIF(\$J\$3:\$J\$300,"L/C",\$I\$3:\$I\$300)-SUMIF(\$J\$3:\$J\$300,"L/D",\$I\$3:\$I\$300)

the above basically lets me calculate the total but as no - and + signs were input it does this by recognising the 'dr' and 'cr' in the column next to the amount.

Determine Which Values/Cells Sum To A Total Amount

Apr 25, 2008

Is there a function, or how would I write a vba to figure out the following.

I have 86 items all with a different price, which come to a total of \$348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of \$57,673.36

Formula That Calculates AVG Number Based On Exact Criteria

Jan 14, 2014

Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?

Please see an example:

Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer

[Code] .....

I guess it has something to do with AVG and IF formulas.

VBA Code - Cell In Header Equal Total Amount

Mar 5, 2014

I have a report that I want the total amount (in the bottom right corner, but can vary in row number) to appear in a particular cell in the header. In the macro, I selected the cell & entered "=CTRL+End". It works in reality, but not in a macro. It returns a visual of 0.00, but "=XFB1048565" in the formular bar. How can I get the header cell to return the correct total amount?

Prorate A Total Dollar Amount By A Selected Number Of Attendees

May 29, 2009

I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.

Formula Returning Name, Calculates The Count And Value Of Line Items Based On A Report

Dec 5, 2007

I have an excel spreadsheet which calculates the count and value of line items based on a report. I use the sumproduct function to calculate this as it involves multiple criteria. The formula is use is

SUMPRODUCT(('Report 50'!\$C\$2:\$C\$64992= Dashboard!\$B172)*('Report 50'!\$H\$2:\$H\$64992=Dashboard!G\$2)*(LEFT('Report 50'!\$S\$2:\$S\$64992,14)="credit transfe")*('Report 50'!\$L\$2:\$L\$64992=15))+SUMPRODUCT(('Report 50'!\$C\$2:\$C\$64992=Dashboard!\$B172)*('Report 50'!\$H\$2:\$H\$64992=Dashboard!G\$2)*(LEFT('Report 50'!\$S\$2:\$S\$64992,14)="credit transfe")*('Report 50'!\$L\$2:\$L\$64992=19))

I think the possible error is coming out of this criteria (LEFT('Report 50'!\$S\$2:\$S\$64992,14)="credit transfe")

Out of one particular field i am picking those line items in which the first 14 characters are "Credit Transfe", if i eliminate this criteria i dont get error.

But this is the main criteria for my calculation. Most of the time i dont get any error , but sometines i get the # Name Error? When i double click on the cell it goes to one column. I dont know what to do from there.

Calculate Total Amount Of Kilos For A Specific Date With Given Time Range

Mar 26, 2014

I am trying to calculate the total amount of kilos for a specific date with a given time range.

As well as the average time they have been handled with in the same specifications.

Attached is a sample sheet of the info I am working with but I cant seem to get the formulas to work.

Book1.xlsx

SumIf: Check The Total Budgeted Amount Based On The Values I Enter On The Categories Sheet

Dec 18, 2009

I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.

Returning A Value If The Dates/times In Two Cells Are Between X Amount Of Hours

May 18, 2009

I have a spreadsheet used for calculating information based on the dates specific shifts are requested/cancelled by our clients. I have a formula for working out if a date & time of cancellation is less than 48hrs notice of the shift starting. This is because we have cancellation fees based on this.

What I have is this formula: =IF(A16="","",IF(INT(A16)-INT(G16)<2,1,"")) that returns a 1 if that shift is cancelled within 48hrs notice. This works fine but I have to now change the notice periods to the following:

72hrs+ - return 1
48hrs-72hrs - return 2
13.5hrs-48hrs - return 3
0-13.5hrs - return 4

edit this formula to take this into account? I figure it's using multiple IF's and changing the <2 into something else like the number of hours but I'm not sure of the exact syntax.

Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product

Jan 7, 2010

I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.

Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.

I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.

Formula To Multiply Total Number Per Category With The % Of Total?

Dec 13, 2013

number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.

Calculating Rent By Year

Dec 12, 2006

I have attached the spreadsheet in a pdf. I can e-mail it to anyone who is is willing to help me.

I am trying to calculate the amount of income per calendar year from each tenant.

I have, for each tenant, a commencement date (the date that the first lease year began) and a termination date (the date the last lease year ended). If a tenant has signed a 10 year lease, for example, and his lease commenced on February 15, 2005, then it will terminate on February 14, 2015. The 10 years in that range each have a different rent for that year. Year 1, (from February 15, 2005-February 14, 2006) he will pay \$4k a month. In Year 2, (from February 15, 2005-February 14, 2006) he will pay \$6k a month. What I want is a set of columns that show the total rent he will pay in 2005, 2006, and so on. The 2005 total should be: \$42,000 (10.5*\$4000). In 2006, it should be: \$69,000 (1.5*4000 + 10.5*6000), and so on to year 10 (being split between 2014 and 2015. Also, please note that the lease could commence on any day of the month and they pay the corresponding pro-rata share of rent based on the number of days into the month the Lease Year is starting/changing.

The result should look like this:


[\$42000][\$69000]
next tenant amounts
next tenant amounts

Calculate Current Rent

Jul 13, 2009

Is it possible to have my lease rents be automatically adjusted on the anniversary of their commencement date using the current system date? Maybe, this would work using the excel TODAY function. Excel would need to compare the current date with the commencement date and calculate how many years have since passed. It would need to use a compound interest formula to calculate what the present rate is.

In the document attached, you will find (lease commenced, original rent, escalation rate and present rent. There are also ammendments which follow the present rent which show how rent has increased due to new lease agreements or amendments. The most recent rent to use is present rent, but it would be great if excel could show how the rent increased or changed since the lease start.

The document is the following: Basically, these are multi-year property rentals which on the anniversary of the contract signing increase by 4% or 5% annually (or whatever the escalation is). That means, if I rent it out on March 1st 2007 for 1000\$, it will be \$1050 from March 1st 2008 and then 5% increase on every anniversary. It works just like compound interest over given years.

What I want to know is it possible to have Excel update my spreadsheet depending on the date automatically. Is there any kind of Macro program? I have over 600 rents, that is why I want the process automated. I would want the rents to increase on the anniversary of the contract signing every year and so basically the Excel would update itself, or it would update once I turn it on and then refresh it.

Next question I have is, say I scroll my mouse over the rent column. I look at building A and look at the rent for this year. Is it possible that once I pop my mouse over it a small chart can pop up and describe the rents for the previous years. Meaning a chart that would quickly pop up and show the 5% decrease every year for the past say 5 years.