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
ADVERTISEMENT
May 6, 2014
Find attached , formula on d2 and e2 , raw data sheet1
Attached Files : counting seller paid.xls
View 3 Replies
View Related
Aug 22, 2008
I have two excel sheets. The first lists all orders submitted. The second lists all orders paid, or pending payment. I would like a column in the first sheet that would show if that order was paid, by pulling the info from the second sheet.
View 14 Replies
View Related
Jan 22, 2007
I am trying to sum rows based on unusual criteria: i.e. A table of date c2:t9 contains details of expenditure for 16 periods for insurance and motor expenses. i want to convert this to a cashflow format and reflect when the item is actually paid, so if it is paid in february, march, etc (given in the original table) I want to add the totals in the months preceding the set date. also, if the criteria is a number <12 then i want to add up the preceding amounts if the period number is a factor of that number e.g - criteria is 3, then i want to add rows 1-3 and put in period 3, 4-6 and put in period 6.
View 6 Replies
View Related
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
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
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
Jun 9, 2014
I need to write a formula that will look at a number in a cell and compute an amount at 1% up to 55,000. Then anything above the 55,000 compute an amount at 3.25%. Then add the two together.
So a baseline of 55,000 at 1%. Then above the baseline 3.25%.
View 3 Replies
View Related
May 15, 2009
I have this code which need to capture Rank & URLS of given keywords in Google paid search but now its not capturing any rank & urls, its gives no data found. solve this and modify it in such way that it record the position of the paid advert on the google page, advert title and description and then click on the ad to capture the actual landing page (not the landing page url shown in the paid ad).
View 3 Replies
View Related
May 27, 2008
I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.
I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.
For example
Balance | Rate | Payment | Time Period | Interest Paid
$1,000 | 10.00%|$20.00 | 6 | $48.56
View 9 Replies
View Related
Nov 26, 2009
I'm trying to mark up in advance the dates where i'll get paid. I get paid monthly on the 2nd last working weekday of the month. So because we have the 30/11/2009 falling on Monday of next week, my pay day for this month is Friday 28/11/2009.in December it should be the 30/12/2009.
I'm getting all tied up in Networkdays, EOm and weekday function!
View 9 Replies
View Related
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
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
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
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
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
Mar 25, 2014
In excel 2007 i need to do a formula...
I have the number of hours and de couste...in an other cell i have the expression that says that is paid or not....
I need only sum the cells that have the expression paid ...how do i can do this?...
View 1 Replies
View Related
May 26, 2009
I have an issue on manipulating the data in my worksheet below. I need to update column 'remark' when the customer has pay fully. Example customer 1003 and 1004 both pay completely so I need to update done in column remark. For customer 1002, because not fully pay, so cannot update done.
customer inv no invoice payment remark
1002 A12 100 100
1002 B45 120 0
1003 W12 90 90 done
1004 F12 30 30 done
1004 F17 45 45 done
May I know what function or coding can achieve this?
View 3 Replies
View Related
Feb 11, 2009
Most Recent Paid EMI Date:
I need to get the Most Recently paid EMI in the List it should be in tune with the TODAY() as well as the PAID one indicated by "X" in Column F..
Next EMI DATE:
This would be the NEXT EMI date as the name suggests after the Most recent paid EMI…In this case 14-Feb-09
Date to Replenish:
This date would be when the Amount in the Account has got reduced indicated by "X" in column E...In this case 10-May-09.
View 8 Replies
View Related
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
Feb 3, 2014
I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.
I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.
- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.
- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?
View 1 Replies
View Related
Jun 1, 2014
I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount
1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set.
Total each set.
2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"
3) formula to find all postage totals and combine for a grand total per category.
usps15.23postage
usps14.32postage
usps5.23postage
fedex5.25postage
fedex10.22postage
shell45.28fuel
shell99.38fuel
qt27.38fuel
qt44.88fuel
View 7 Replies
View Related
Dec 4, 2008
I would like to be able to count all the closed transactions for the month of May and then add Column B if they match with May ....
View 9 Replies
View Related
Feb 26, 2014
I have attached a spreadsheet which shows my desired result which often works.
Range C2:C13 is my desired result...
The problem /criteria is...
For every 140 meters sold the sales person receives £140 commission.
Meters (range B2:B13) can be carried to future months.
I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.
View 5 Replies
View Related
Apr 8, 2006
I work for a company that owns hotels and casinos all over the States.
Company wide I would say we have about 13,000 employees. The hotel I work at
has around 125 employees (even w/ the constant turnover after Katrina hit).
My problem is, that my corporate office refuses to update our payroll
operations. We still use punch time cards that every other Friday I have to
manually enter time into the payroll server timesheet. Our system does not
calculate paid time off, among a lot of other things. My employees come to
me almost daily asking if I can tell them how many PTO days they have left.
The only way I have to figure it out is by going through every PTO bi-weekly
request form spreadsheet until I have added up the days taken throughout
their service year.
I am trying to build a spreadsheet in Excel 2003, that consists of:
Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date,
PTO per yr, PTO taken, PTO days left....among other personnel information.
I want to create a formula that will calculate the PTO per yr the employee
has.
After 6 months = 2 days
1 year = 10 days
2 years = 14 days
6 years = 19 days
16 years = 25 days
26 years = 30 days
(If the employee does not use the days within their service year, they loose
it.)
I also want in the PTO taken column a formula that will pull the # of days
taken from the PTO request spreadsheet that I have to send in to home office.
The employee's differ every spreadsheet, as does their placement. Once the
days taken are in their proper column, I want the PTO left to be calculated
from PTO days per year minus the days taken.
View 9 Replies
View Related
Dec 11, 2012
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
Manual Invoicing Query.xlsx‎
View 1 Replies
View Related
Jun 7, 2009
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.
PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.
View 7 Replies
View Related
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
View Related
Jan 8, 2004
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:
Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+
I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
View 9 Replies
View Related
Sep 19, 2006
I have 12 different sheets named April-06-sales through to march-07-sales. I need is to be able to search all of the sheets and check if the invoiced paid column is blank and if it is copy that row into a new sheet. All of the sheets have a different number of rows.
View 2 Replies
View Related
