Form Sub-totals Quarterly In Typical List Of Transactions?

Feb 21, 2014

How to form sub-totals quarterly in a typical list of transactions. Twist being it is not calendar year quarters but quarters from from 5 April to 5 July, 6July to 5 October etc. Typical table columns looks like:

Date Amount Transcation
5/4/14 200.50 bolts
7/5/14 50.10 screws
6/6/14 10.00 bolts and nuts
--------------------------------------------
SubTotal 260.60

12/7/14 10.00 bolts and nuts
10/8/14 40.00 rivets
4/10/15 10.00 screws
--------------------------------------------
Sub Total 60.00

View 1 Replies


ADVERTISEMENT

Calculating Quarterly And Yearly Totals From Dates?

Jan 5, 2012

I have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?

View 1 Replies View Related

Calculating List Totals

Mar 4, 2012

I have an Incomes & Expenditures spreadsheets. When I add an expenditure to the sheet I can choose which category of expense it is from a drop down list of expenses. At the lower part of the sheet away from main section I have a list of the categories which I would like to have monthly totals alongside. with the formula?

View 7 Replies View Related

Pulling Totals From List

Jun 17, 2006

how to easily pull numbers out of a column in excel. I have several sets of numbers with a total in column F. I want a macro to go in and pull the last row in each set (this is the total row) and paste them in a different column so I can have the totals only.

View 2 Replies View Related

Formula To Get Totals From List Of Dates

May 23, 2014

I am trying to write a formula for my account statement its got a list of dates of invoices descriptions then the value of invoice.

I want to check the dates of invoice to the date of statement if less the 1 day invoice total to go in a box called current, if between 2 and 30 days to invoice value to go into 1 to 30 days past due, if between 31 and 60 days invoice values to go into a box called 31 to 60 days past due, and then if dates are between 61 days or more then invoice totals go in to a over 61 days over due box.

View 1 Replies View Related

List Top 10 Records According To Count Totals

Dec 25, 2006

How do I create a formula that will list the records with 10 highest results
and if a tie, list the tie record with the lower value next.
Each record is is double digit value from 00 to 99:

- the records are in range ff7:ff106
- the record results in range FG7:FG106
- List the highest rankings in FH97:FH106

View 9 Replies View Related

Macro To Automatically Add Totals To The Bottom Of A List Created With Advanced Filter

Mar 5, 2008

if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)

View 12 Replies View Related

Add List User Form, List Box And Get Input

Dec 16, 2009

I wrote a macro which creates desired output based on one default variables. But I want to change it to take user selected assumptions and perform the macro for each user selected variables.

I am confused with user forms and controls and what to use.

Can anyone give the code to how to add a list box to a userform and then add items to the listbox, and run macro based on thegiven input in the list box?

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

Tracking Paid Transactions

Mar 25, 2014

I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.

Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.

View 1 Replies View Related

Sorting Paypal Transactions

Feb 16, 2008

I need to sort all my pay pal transactions I need all my debits in one row and credit in another.

View 14 Replies View Related

Counter For Transactions Within Account

May 11, 2009

I need to have a running counter of transactions within an account.

Solved with: C2=COUNTIF($B$4:B4,B4)
(and copy down)
Example:
ACCTNO AMT Trans#
100125.001
100121.002
100122.50 3
1001 2.00 4
1001 5.00 5
100127.00 6
1013 .50 1
1013 2.50 2
1013 13.00 3

I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.

View 6 Replies View Related

Counting Transactions Per Agent

Sep 3, 2009

I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1.

I tried sumif, but its totalling the amount. but i wanted is the number of transaction.

View 9 Replies View Related

Automate Quarterly Calculation

Jul 29, 2009

how do i get excel to automate quarterly calculation? in column A1 I have Year and column B1 i have months (1-12) i have data in column D1 - H124 - I would like to run a macro where it will pick up Jan, Feb, march (1,2,3) for each year in column A and do a quarterly average right now i manually use =(D35*31+D36*28+D37*31)/90 but i would like to avoid human errors can i get excel to read if column B is 1,2, and 3 run quarterly average? and would excel know that in Feb there is only 28days except leap years?

View 9 Replies View Related

Compare Combinations Of Values / Transactions

Jan 25, 2014

I need to compare in and out of money in multiple bank accounts.

Imagine in row one i have all the "INs"
and in row two i have all the "OUTs"

Now, how do i compare say first transaction in row one to say 5 transactions in row 2 and find the relationship
It can be:

1. Transaction IN 1 = Transaction OUT 3
2. Transaction IN 1 = Transaction OUT 2+4
3. Transaction IN 1 = Transaction OUT 3, or Transaction OUT 2+4

So if its a direct relation it just displays where they are equal, if they aren't how it will display which multiple transactions will be equal, and if there are 2 different possibilities it will show both answers.

If its only In = Out its pretty straight forward, but how do i code it to search for combinations of transactions say 1+2+5 efficiently.

View 5 Replies View Related

Combine Quarterly Bars Get Messed Up?

Feb 5, 2014

Each quarter should have the bars standing next to each other, i.e. Q1 2013 should stand right beside Q1 2012. Between these pairs of quarter there should be a small space and the the next pair, Q2 2013 and Q2 2012, should be. So how do I do this?

When I use data without stacking several info in the same bar, this isnt a problem but when I have this "aaa, bbb,ccc,ddd" in every bar, this gets messed up.

View 2 Replies View Related

Monthly To Quarterly Cash Flows

May 13, 2014

I am building a Discounted Cash Flow.

I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.

View 1 Replies View Related

Conditional Cell Color Changes And Transactions

Sep 7, 2009

1.1 In columns N and O, color the numbers in both the N and O cells green if, and only if, (a)the N cell's number is greater than O's, AND (b) both N and O cells' values are greater than the preceeding N and O cells' (i.e. a great value than one row higher in the column).

1.2 In columns N and O, color the numbers in both the N and O cells red if, and only if, (a)the N cell's number is less than O's, AND (b) both N and O cells' values are lesser than the preceeding N and O cells'(i.e. lesser values than one row higher in the column).

View 4 Replies View Related

Finding Difference In Timing Between Transactions

Oct 13, 2009

I have approximately 40 seperate sheets in one workbook. Each sheet is a unique part #. Each part has 6 different types of transactions possible. Let's say A-F. A-F each have a date associated when them of when the transaction occured. The transactions are sorted by date. I would like to write a formula that when Transaction A occurs what is the diffence in days until D transaction occurs. Or the time differnce between when B occured and the next F occured.

below is my datedif formula, but it obviously only works in a sequential order from top to bottom.

=IF(DATEDIF(M5,M6,"y")=0,"",DATEDIF(M52,M6,"y")&" years ")&IF(DATEDIF(M5,M6,"ym")=0,"",DATEDIF(M5,M6,"ym")&" months ")&DATEDIF(M5,M6,"md")&" days"

View 12 Replies View Related

Quarterly Interest Calculating Formula

Oct 21, 2009

I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.

But I want to make it compact , as d one I created is long enough.

A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest

Now in F3 I want the Interest amount , compunded quarterly.

Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?

View 8 Replies View Related

Monthly And Quarterly Averages From Another Sheet

Apr 15, 2014

I need a formula that will provide monthly and quaterly averages from another worksheet, but only include the months that have occurred. I have the formula to obtain both the averages and quarter totals, but it is counting all months w/in the quarterly range before the months have occurred.

View 5 Replies View Related

Quarterly Goal Pacing Calculation

Dec 8, 2012

I am very new to excel and my formulas dont add up.

I need to calculate based on my quarterly goal and my quarter to date actual sales what my percentage pacing is.

Now the way i did it is =B5/A5

Do i need to add the Quarter date calculations in there because its not giving me the accurate percentage.

Goal Actual Pacing

View 3 Replies View Related

Info For Transactions - Calculate Commissions

Aug 23, 2013

I have a spreadsheet with detail info for transactions. There are multiple columns...but these are the ones I'm concerned with. ie

Cell A4 has the date range ( 1 month) ie "04/01/2013 - 04/30/2013"

below starts on B5
Cell B - Cell D - Cell I
vendor - Qty - Profit
H20 Month $50 2 7.00
H20 $30 Mo Unl T&T 2 4.20
H20 Month $60 21 88.20
Page Plus Unl $55 3 22.29
PagePlus Unl $39.95 6 32.34
etc...
Cell A32 has the date range ( 1 month) ie "05/01/2013 - 05/312013"

and the vendor, qty and profit like above again....

The above

To the right of this I have :

Cell L2 Cell M2 Cell N2 Cell O2 Cell P2 Cell Q2 Cell R2
Company - April - Topup profit - May Topup Profit - June - Topup profit
H20 29 110.60 71 261.10 93 342.95
PagePlus 19 55.05 25 106.72 14 44.70
etc...

at the bottom, I have the totals for each cell from M2 thru R2.

How can i get L2 thru R2 to sum up the detail amounts on Column I by Column B ?

View 1 Replies View Related

SUM Monetary Transactions Based On Two Criteria

Feb 20, 2014

I want to SUM monetary transactions based on two criteria.

1.) if the transaction occurred within a certain month (Jan)
2.) based on the transactions category ("Obligations")

I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.

=SUMIF(REGISTER!E3:E1000,"Obligations",REGISTER!G3:G1000)

=SUM((IF(MONTH(REGISTER!$B$3:$B$1000)=1,REGISTER!$G$3:$G$1000,0)))

View 9 Replies View Related

List Not Showing In Form

Sep 30, 2008

I have a form that is built based on the number a person enters. For Example - if the person enters the number 2 then 2 ComboBoxes are entered on the form.

The problem that I am having is that the list is not showing when the form is loaded. In the Project Screen all is OK until the final step when the form is Shown.

View 7 Replies View Related

Form One Unique List

Mar 10, 2008

I have two groups of data listed in one sheet. I have supplier names down the side and locations across the top, with data filling the respective cells. Refer excel file attached. At the moment I am manually merging the two lists to form one. I am copying both rows of headings and creating one unique row heading list, deleting any duplicates, then cutting and pasting the data under the respective heading. Because I am going to have to do this every month when new data comes in, I want to know whether their is a way to create a macro to automate this process...

View 2 Replies View Related

Counting Paid Transactions Per Seller Database?

May 6, 2014

Find attached , formula on d2 and e2 , raw data sheet1

Attached Files : counting seller paid.xls

View 3 Replies View Related

Summarize Quarterly Data In The Summary Table?

Aug 7, 2014

summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.

View 4 Replies View Related

Lookup Quarterly Periods Against Multiple Dates?

Feb 26, 2014

My lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.

View 2 Replies View Related

Transactions Over A Month Broken Down To Each Product Type

Apr 27, 2009

I have a set of sales data which shows the dates of transactions and also the product type that was sold. I want to see the monthly sales for each product type. I can get a total for all product types over the months using the following:

=COUNTIF(Licences!E2:E9999, "<39600")-COUNTIF(Licences!E2:E9999,"<39569")

36900 = Jun-08
39569 = May-08

But I need it to also break it down for product a, b, c so I need something else to add to the formula?

View 6 Replies View Related







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