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.

View 2 Replies


ADVERTISEMENT

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?

View 9 Replies View Related

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?

View 13 Replies View Related

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% !?!

View 9 Replies View Related

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?

View 9 Replies View Related

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?

View 7 Replies View Related

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%

View 9 Replies View Related

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.

View 2 Replies View Related

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.

View 9 Replies View Related

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.

View 2 Replies View Related

Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total

Mar 6, 2013

I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.

Link for excel file is here: [URL] ...........

View 1 Replies View Related

Pulling Data Into Two Columns Labeled “Monthly” & “Non-Monthly”

Aug 3, 2009

I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”

The Monthly data is obtained using the following formula:....

View 9 Replies View Related

Formula For Getting Monthly Total

Aug 14, 2014

Find the attached excel sheet.

I have, every month data like for one Year, every day in a month we will update the sales information in to excel sheets like jan, feb, march, ...December

In the Main sheet i want total, i have bunch of data and wide range of items, how to get monthly consumption into main sheet.

excel.xls.xlsx‎

View 1 Replies View Related

Formula For Monthly Totals

May 20, 2009

I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.

I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.

I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.

Attached is my example.

View 6 Replies View Related

Formula - Monthly Budget

Mar 5, 2009

I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...

What I am trying to do is:

Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)

What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..

For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".

View 9 Replies View Related

Formula For Monthly Data Into Quarters?

May 25, 2014

I believe there is a way to summarize monthly data with the month in rows and the heading across columns. The goal is to have a formula summarize the data with Q1 through Q4 in rows and headings across columns and vice versa. Sample data attached.

Sample.qrt.data.jpg

View 2 Replies View Related

Put A Formula In Each Cell Of Monthly Overview?

Dec 9, 2013

Basically what i need to do is put a formula in each cell of "Monthly Overview" that references the correct property and monthly total from the "Monthly Billing"

Example...."Monthly Overview" C2 needs to reference "Monthly Billing" for what was billed to Apex Broadcasting (B column) in Jan (C Column)

View 6 Replies View Related

How To Add Monthly Sales Data Using Formula

Dec 29, 2013

How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.

mockup.xlsx

View 5 Replies View Related

Formula For Monthly Average With Different Ranges?

Feb 5, 2014

I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.

View 8 Replies View Related

Formula For Interest Compounding Monthly?

Feb 20, 2014

If i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?

View 2 Replies View Related

Formula To Calculate Monthly Deduction Based On Date

Jan 30, 2013

I'm looking for a function that calculates a fee deduction based on the 28th of each month.

I'm paying back £200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...

A
B
C
D

1
£1,300.00
=TODAY ()

2
-£200.00
28/02/13
FUNCTION

How to do it but basically I'm trying to put a formula in D2 as follows:

If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2

How to continue it calculating reductions per month by duplicating the formula...

View 1 Replies View Related

Conditional Formula- Worksheet With Monthly Sales Figures

Aug 5, 2008

I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.

I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.

This is basically what i'm trying to do:
If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?

View 9 Replies View Related

Sumproduct Formula: Multiply Monthly Values With A Maximum Value In Any One Period

Jun 16, 2009

Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

View 2 Replies View Related

Formula To Identify Data Type And Calculate Monthly And Cumulative Figures?

Jun 7, 2014

I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.

If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.

View 2 Replies View Related

VBA Calculates Sum If From CSV Workbook

May 2, 2014

Sample stock system LOADTESTING.xlsm has ten products. "LoadTesting" shows the opening stock for each. CSV file "Van Sales" has the Stock Number in Col M and the sales figure in Col N. I want the code to Open a CSV file "Van Sales" (which for some reason the site would not upload) Establish the sum total sold for each product (VBA equivalent of SUMIF) Deduct that sum for each item from the opening numbers on "Van Sales" (e.g. Product 001 started with 156 on the van, 92 were sold, so the "revised" van figure should be 64).

Can't get the code to link to the right "ranges" from the Van Sales workbook.

(You will have to change the file path calling the Van Sales file, but if you do get it to work,

VB:
Option Explicit
Sub GETSALE()
Dim Dest As Range, Dest1 As Range, Dest2 As Range, Dest3 As Range, oCell As Range, varResult As Variant

[Code].....

View 4 Replies View Related

Calculates Faster Than IF

Aug 21, 2009

I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..

View 7 Replies View Related

IF Function Only Calculates First Value?

Sep 9, 2013

I have written an IF formula that only clacluates the first IF values in the formula and I dont know why.

Here is my formula: =IF(I2999;"1000";IF(I2>1999;"2000";IF(I2>2999;"3000";IF(I2>3999;"4000")))))

So if in I2 I have the value 800 it correctly returns

View 7 Replies View Related

Writing Formula To Calculate Monthly Returns Based On Annual Growth Rate

Jul 10, 2014

I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.

writing a formula to evaluate over 360 periods.

View 5 Replies View Related

Query- Worksheet When The Program Calculates

Oct 17, 2007

If I have a worksheet that has a figure when the program calculates, how do I set up in another worksheet so that it tells me how many pages to print (that has information based on the 1st spreadsheet).

View 9 Replies View Related

If Statement: Calculates 7.25 By The Hours Worked No Matter What Comes Up In F2

Dec 15, 2009

The whole document works but the last one i need. I'll post it all just in case i have an error elsewhere.

Start Time -- Stop Time -- Break -- Total -- Hours Worked -- Average -- Pay
6:00 -------- 3:00 ------- Yes ---- 1600 -- 8.00 ----------- 200 ------ $xx.xx

E2 Formula for time: =IF(C2="Yes",(((A2*24+12)-(B2*24+24))+1),((A2*24+12)-(B2*24+24)))

F2 Formula for average: =D2/E2. and now for the problem one. G2 Formula for pay: =IF(F2<165, E2 * 7.25, IF(F2<180, D2 * 0.07, IF(F2<190, D2*0.08, D2*0.09)))

Just to break that formula down more:
0 - 164 = 7.25 * Hours Worked
165 - 179 = 0.07 * Total
180 - 189 = 0.08 * Total
190+ = 0.09 * Total

it only calculates 7.25 by the hours worked no matter what comes up in F2

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved