Chart Illustrating Accounting Figures Of X Weeks Over A Year
Aug 6, 2008
I have to illustrate that the average life of a set of accounts is 6 weeks. I figured that the best way to add significance to this length of time is to show in either a pie, column or bar chart the relevancy of 6 weeks within a 52 week year.
Sounds fine, right? Well, I went blank after that. I couldn't think of how to set up my worksheet in order to generate a chart of this nature.
So, I guess I'm wondering how you guys would set up a table that could then be used as the source for either of the aforementioned charts.
View 8 Replies
ADVERTISEMENT
May 12, 2009
I'm trying to come up with a formula that calculates a year to date target, taking into consideration the actual working days and the target for those days.
So for instance today is the 12th of May and my year to date percentage is 40% but it should be 100%. How do i create a formula to calculate that?
View 9 Replies
View Related
Mar 10, 2009
I am creating a worbook to generate month end reports. I'd like to pull figures from the various budget sheets into the reporting sheets for the current month and year to date by setting up a variables sheet and I think the "Choose" function but my experimentations have proven me incapable of doing so.
What I'd like to do is set my month in the variables sheet to the month I want to report in, eg "Feb" and have the budgets pull through automatically.
View 4 Replies
View Related
Mar 14, 2014
I have just changed from XP Pro to Window 7 and therefore using an updated version of Excel. When, as previously, I try to move columns of figures across the worksheet to become comparatives by using Control C and Control V it doesn't work.
View 2 Replies
View Related
Jun 3, 2006
I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.
View 14 Replies
View Related
Sep 29, 2006
I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
View 9 Replies
View Related
Feb 27, 2009
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.
i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.
What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
View 2 Replies
View Related
Sep 7, 2009
i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
View 7 Replies
View Related
Jul 3, 2014
I have a set of data with values and dates (call the dates 'raw dates'). I have added 3 columns with formulae in them as follows:
Year =YEAR('raw date')
Month =TEXT('raw date',"mmm")
Day ='raw date', formatted as "d".
I have a pivot chart with three Axis Fields. In order they are: Year, Month and Day. This is so that the chart itself has three layers of x-axis names (year, month and day) rather than a whole load of dates which looks messy.
I would like to show the values as a running total "in" the raw dates of the data.
When I show the values as "Running Total in" 'raw dates', I get a load of #NAs in the pivot table because the raw dates are not in the Axis Fields. If I select Running totals "in" the Days (which is included in the pivot table setup), I get running totals over each month, starting at zero on the first date in each month. I need to have the running total run over the course of the whole data set, not mini running totals in each month.
When I choose only the 'raw dates' in the axis fields (i.e. taking out Year, Month and Day), running total works fine. The only thing is that I lose the nice looking x-axis. Adding the raw dates to the other 3 Axis Fields also doesn't work - it doesn't even try to give a running total at all, just the original values.
how to achieve a running total without sacrificing the x-axis?
View 1 Replies
View Related
Sep 25, 2006
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
View 3 Replies
View Related
Dec 8, 2013
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
View 4 Replies
View Related
Mar 8, 2014
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
View 4 Replies
View Related
May 14, 2009
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies
View Related
Jan 28, 2010
I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.
For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.
I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.
View 7 Replies
View Related
May 20, 2014
I am trying to make an accounting spreadsheet to be used with the game monopoly for my business students.
Each row I want to copy to a separate sheet within Excel.
For instance the row with the account title cash I want that to copy entire row to the cash worksheet. "go" revenue to the "go"revenue worksheet etc.
Date
Account
Debit
Credit
May 20
Cash
200
[Code] .......
Here are the sheets I set up to transfer to depending on the account title:
Cash
Properties
Acct.Pay. Monopoly Loan
"Go" Revenue
Rent Revenue
Misc. Revenue
Rent Expense
Misc. Expense
View 4 Replies
View Related
Jun 26, 2014
I have formatted several cells with an Accounting style. However, the dollar sign doesn't show up until I click in the command line.
View 5 Replies
View Related
Aug 15, 2013
I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.
The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.
My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?
Below is a strip down version of the code.
VB:
Sub test()[COLOR=#333333]
[/COLOR] Dim rLookInADR As Range
Dim foundcell As Range
Set rLookInADR = Range("b1:b380")
Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
MsgBox (foundcell.Row)
End Sub
[COLOR=#333333][/COLOR]
View 5 Replies
View Related
Dec 4, 2013
Looking for a way to correct what is going on with row 9. I need it to return either zero or empty, but also include the current formula.
View 3 Replies
View Related
Jun 25, 2014
I just downloaded an income/expense template from Zillow to manage my rental properties. The template works great but it's only set up for 5 houses and I need more columns as I own more than 5. In the attached template the houses are listed by property codes and that is the column that I need to add to.
View 8 Replies
View Related
Dec 4, 2008
IF B1 has a possible value ranging from 1 - 5, and IF the value in E1 is equal or great than 2,5,10,10,15 BUT 2,5,10,10,15 need to match to specific ranges set in B1 1=2, 2=5, ,3=10 ,4=10 ,5=15 THEN IF TRUE "WITHIN" IF FALSE "NEEDS UPDATE"
Got help earlier with this formula: =IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE") :D Worked Great! This was my attempt at expand that formula:
View 3 Replies
View Related
Jan 15, 2009
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
View 4 Replies
View Related
Mar 26, 2009
i am doing some calculations using vba in excel, i need to know how to set the cell format to 'accounting' in vba ?
View 2 Replies
View Related
Oct 31, 2013
I'm using this custom format
Code:
_-£* #,##0.00_-;[Red]_-£* #,##0.00_-;_-£* "-"??_-;_-@_-
How do I modify it so that if the value is 0.00 then 0.00 is showed and not the current "-"?
View 3 Replies
View Related
Nov 16, 2013
I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.
View 5 Replies
View Related
Apr 22, 2007
I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.
View 9 Replies
View Related
Jan 18, 2009
I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set.
Dim Arrayone(0 To 9) As Integer
Dim Arraytwo(0 To 9) As Integer
Dim Arraythree(0 To 9) As Integer
Dim Arrayfour(0 To 9) As Integer
Dim Arrayfive(0 To 9) As Integer
Dim Arraysix(0 To 9) As Integer
Dim Arrayseven(0 To 9) As Integer
Dim Arrayeight(0 To 9) As Integer
Dim Arraynine(0 To 9) As Integer
Dim Arrayzero(0 To 9) As Integer
Dim Arraytwotest(10 To 99) As Integer
Dim x, I
Dim Row As Long, Col As Long, Step As Long, Colcells
Dim Digits As Long, Total As Long
View 9 Replies
View Related
May 7, 2009
I am trying to determine if a specific date is what week in a 4-4-5 accounting month. See attached file. Is there a formula that can be used to do this???
View 4 Replies
View Related
Jan 2, 2010
I have a custom sort order that I use for sorting military ranks in a number of massive spreadsheets I am using. Normally the data we get has a long version for the ranks (column 1) and a rank code (column 2). When the rank code is there it is simple to sort by that column in descending order, but without it I need a custom sort. The problem is there are up to three equivalent ranks at each level across Army, Navy and Air Force as you can see in the attachment.
Is there a way I can set up a custom sort based on both columsn of data but sorting only on the second column. I could do vlookups but there are up to 50,000 rows and I don't really want to keep that formula there, and as I use it across multiple sheets (yes I know all one sheet, but it is segregated data and used in different situations) including a vba written value would have to occur for every row on every sheet - not the best option in my opinion - maybe it is.
View 9 Replies
View Related
Jun 28, 2006
I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?
Windows("0285 WORKING FILE 0406.XLS").Activate
Sheets(3).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R501C1"
ActiveSheet.Paste
Sheets(2).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R1001C1"
ActiveSheet.Paste
Sometimes I will receive this file and there will be additional tabs. I just never know how many.
I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.
I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.
For each new worksheet selected add 500 rows to the last pasted amount."
View 7 Replies
View Related
Jul 20, 2006
If I would like to compute a running balance, how do I make the balance display conditional so that I don't have the balance displayed all the way down the page? I like to copy the formula and the formatting down the page, but I don't want to see the running balance unless I have entered info in the row.
View 3 Replies
View Related
