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


ADVERTISEMENT

Months Formula - Remaining In Quarters

Apr 11, 2014

In Column A, I have the dates listed as such. Common point is that, they are all 1st day of the month.
In Column B, I have Quarters as such.... (Please see below)

Can I put a formula together, to round up how many months left in that particular quarter ? example

Date Q3 2014 Q3 2014 Q1 2015
9/1/2014 1 3 3

Column A
9/1/2014
12/1/2014
6/1/2015
9/1/2014
9/1/2014
6/1/2015
9/1/2014
9/1/2014
...
..

Column
Q2 2014
Q3 2014
Q4 2014
Q1 2015
Q2 2015
...
...

View 9 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

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 To Convert Monthly Data To Weekly Data

May 8, 2014

I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation

Example:

Month123456789101112
Value 2345678910111213

And get it like:

Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3

and so on.

Since I've got a lot of variables I'd like to use some kind of formula.

View 11 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

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

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

Percentage Difference From And Quarters

Apr 29, 2014

Pivot Table where I am comparing prices with previous quarters using the % Difference from and using Quarter/previous as the base.

The function works fine but I can't get any values on Q1 to compare with Q4 of the previous years. All Q1 for every years show no % difference.

View 2 Replies View Related

How To Auto Fill Quarters

Jan 7, 2014

I need to auto fill quarters but Excel fills years instead. For example I write Q1-11 in cell A1 and when dragging down it fills with Q1-12 rather than Q2-11.

View 5 Replies View Related

Return Quarters From Dates

Apr 12, 2012

I have a list of Dates (01-OCT-2011, 03-Dec-2011, 12-Jan-12, 10-FEB-2012, 03-APR-2012, etc). I would like to figure a formula that would see the the date and return the QUARTER. For example:

IF dates are between 01-OCT-2011 thru 31-DEC-2012 THEN return QTR1, IF dates are between 01-JAN-2012 thru 31-MAR-2012 THEN return QTR2, IF dates are between 01-APR-2012 thru 30-JUN-2012 then return QTR3, etc

I'm really just now sure how to do the "betweens".

View 8 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

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

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 View Related

Months And Quarters Sums Not Equal?

Jun 26, 2014

My list has dates. I am using this formula to determine the quarter for the date: ="Q"&INT((MONTH(S2)/4)+1).

As well I am using a formula to determine the month for the date: =E2-DAY(E2)+1.

In my book I have some sheets showing totals by month, in other I total up by quarter. I am experiencing a variance between the quarterly total by months and the quarterly total by quarter.

So I thought I'd try this formula to reconcile the two--but no success: =SUMPRODUCT((System=$B5)*(Created>=Lists!M$2)*(Created

View 5 Replies View Related

Calculate Number Of Quarters From Quarter And Year Only?

Jul 27, 2013

I need to calculate the number of quarters difference between two quarter and year values. So I have The following:

Begin Quarter
Begin Year
End Quarter
End Year

3
2005
2
2011

4
2008
2
2013

So I need calculate how many quarters have passed since the begin quarter and the end quarter.

View 1 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

Auto Populate Quarters By Just Typing In The Number Of Years?

Aug 20, 2014

I need a formula that can automatically populate yearly quarters in separate columns based on a number of years. So if I put in 5 years - excel would populate 20 columns with Quarter 1 - Quarter 2 and so on. If I then change it to 3 years it would only populate 12 columns.

View 4 Replies View Related

Excel 2010 :: Time Between Dates In Years And Quarters (.25)

Dec 18, 2013

how to calculate the time between two dates in years and quarters (represented in .25 increments). I would round down on the .25 increments (3 months) if not fully surpassed.

See attachment on what it should look like. C2 is my calculated column.

View 4 Replies View Related

Change A Date To Its Equivalent In Terms Of Quarters System

Jan 23, 2009

I have the formula where i can change a date to its equivalent in terms of quarters system with a date:

=CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),"Winter","Spring","Summer","Fall")&YEAR(A2)

This is a school year configuration.


ex.
A2 = 10/1/2005: with the formula up there it turns into Fall 2005
i want to be able to add any number of years and the formula will still come up with the quarters system

also i would like A2 to be stationary and create a list of quarters for each year i add on

ex.
A2= 10/1/2005 B2=Fall 2005
B3=Winter 2006
B3=Spring 2006
B5=Summer 2006
B6=Fall 2006
etc.

If this is all possible lastly I would like to negate summer quarters

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

Add Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart

Apr 28, 2009

I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.

View 4 Replies View Related

Quarterly Data To Monthly

Jan 27, 2014

I am trying to convert quarterly data to monthly

The quarterly data columns go: Mar-14, Jun-14 etc
The monthly data columns goes Jan-14, Feb-14 etc

I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14

Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?

View 2 Replies View Related

Counting Monthly Data

Jan 31, 2014

I have a table which has the following columns:

Date - Data1 - Data2 - OtherData1 - OtherData2

I came up with some formulas to count my data monthly. I have 12 tables with this kind of formula in it:

[Code] ....

Where B12 is the year and A213 is my month number. My first try on the "date filter" looked like that:

[Code] .........

And it wasn't working so I thought it was because the 31 wasn't a good idea for non-31-days-months but none of the formulas above are working.

(BTW, IDK why it's not working but I have data in my table for months 10, 11 and 12 and the only calculation tables that are calculating data are the ones for months 9 and 10. The results are the same in these two tables and are counting all my Table1[Data1] and [Data2] (the count is not monthly))

View 7 Replies View Related

Monthly Analysis On Data

Sep 29, 2007

I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).

I am currently doing this manually but I'm sure there must be a smarter way of doing this.

View 3 Replies View Related

Excel 2010 :: Grouping Pivot Dates By Group - Can Change Quarters?

Nov 2, 2011

One cool feature of 2010 is ability to (right click on dates) and Group them into Quarters.

How to change the Quarters to be in line with e.g. Financial Year, as opposed to fiscal years? i.e. have a start date of say June?

View 5 Replies View Related







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