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.
In the code below using msoBarTop places the menubar at the top but it seems to just place it in a new row at the right of all menus.
How or can I dock the menubar next to the last menubar?
I am working on a spreadsheet with multiple tabs. The basic idea is that I will pull some raw data and paste it into my first tab, and then I can refresh all of the other tabs to reflect the newly imported data. I had a problem recently because when I attempted to pull the raw data because the number and order of columns had changed which messed up my cell references.
My other worksheets would contain cell references that said =table1!a1, and I would drag that formula down to have a list of all employee names for example. I would then filter data based on some of the other information in other columns. I was hoping that I could paste the newly exported data into that table and reapply the filters to view the new list of employees. How can I do this even if the order or amount of columns change? This spreadsheet will be used many times by different people so I need to make it scaleable in case the format of the exported data continues to change. I tried to change the raw data into a table and change the cell references to the column name, but it did not work when the order of the columns changed.
I am trying to derive the EMI for a Loan Amount and also chart a PIE graph for the same..
Instead of Manually feeding the values such as :
Variable 1 : Loan Amount : Min : Rs 50,000/- Max :Rs 5000000/-
Each Spin (1 Unit) -Rs 25000/-
Variable 2 : Interest Rate : Min : 5% Max : 30%
Each Spin (1 Unit) -0.25%
Variable 3 : Tenure/Duration : Min : 6 months Max : 30 years
Each Spin (1 Unit) - 6 months
I want to have a Scroll-Bar where the values can change Automatically but am not aware of using the same...
Can someone please help me with a small attachment with the above variables attached to it..The values drawn should be displayed as well above the Scroll-Bar and maybe can make the Min and Max more dynamic by storing it somewhere in the Sheet..
I am looking for Non-VBA solution to do the same..
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
View 2 Replies View RelatedI 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?
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?
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.
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 ?
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 RelatedI 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
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 RelatedMy lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.
View 2 Replies View Relatedgenerating a formula that takes the weekly values in a quarter and uses them to generate a quarterly average. Quarters are split up as follows.
Q#1: 09-11 (First week of September-last week of November)
(Used to generate price for January)
Q#2: 12-02 (First week-Last week)
Q#3: 03-05 (First week-Last week)
Q#4: 06-08 (First week-Last week)
I've attached an excel sheet with some dummy data.
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 RelatedI have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
I'm lookng for a way to add functionality across tabs in a spreadsheet where if you put a date and $ amount in different cells on an expense sheet, it recognizes the date of the amount input and it shows up in another tab with a list of monthly expenses.
View 4 Replies View RelatedI am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need.
I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left.
If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.
I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.
I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).
Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.
Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:
(LEFT($D$2:$D$100,LEN($J9))=$J9)
Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.
Attached file. [URL] ........
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
I'm using a stacked bar chart (in Excel 2010, running on Windows 7) to create a simple Gantt-type chart - with just four or five bars. I've got my chart looking most of the way I want it to, but one thing still eludes me: I'd like to set up the major axis ticks to be quarters of the year (from 1/1/2011 to 4/1/2013). Since quarters are not regular intervals (they are not exactly every 90 days), I can't do this using Excel's standard functionality for choosing axis tick marks.
View 3 Replies View RelatedI have a bar chart which compares two columns of data. the data in one column is fixed to 100. the data in the other column varies. a third column states whether each entry is "A" or "M". Is there a way to include the A or M on the bar chart? Perhaps by having a set colour whether the data is A or M?
View 1 Replies View RelatedWhat I need to do is add a scroll bar to my text box in my form. I used the properties of the text box to add a scroll bar but when you preview it you can't see it. When you click somewhere inside the form it appears and is workable but not when it hasn't been clicked on. How do I make the scroll bar appear from the start. this is a vertical one ad is on a text box within the form
View 11 Replies View RelatedFYI This is cross posted on the VB Forums at [URL].....
I know that you can use the below code to add buttons to 2 of Excel's menu systems,
Code:
Application.CommandBars("Worksheet Menu Bar").Controls.Add
Application.CommandBars("Cell").Controls.Add
And this should add a command bar control of whatever type specified to either the Add-Ins tab of the ribbon or the right-click menu when clicking on a range of cells.
However, I would like to be able to add some command bar controls to the right-click menu when you click on a group of columns. Is this possible?
The graph function in Excel is driving me insane! Is it possible to do as image below shows? If so, how?
As you can see, I dont want to stack the result, but rather compare two bars with the bigger one in the background.
code to remove the titlebar of a userform in Excel/VBA.
Remove meaning the program user must not beable 2 see the titlebars..
I have some code that creates multiple new menu bars for a user which each have there own controls. I need to know which control is selected by the user for onaction. The example below is where there are up to 20 new menu bars with names Tract 1, Tract 2, etc. For each Tract, there is a controlbutton titled Absoprtion and I need to know which "Absoprtion" button was selected to run the "ViewAbsorption". I need to use th For next function because the number of menu bars could vary.
Dim objMenu As Object
Dim objViewMenu As Object
For y = x To 39
Name = "Tract" & y - 19
With Application.CommandBars("Worksheet Menu Bar").Controls
Set objMenu = .Add(Type:=msoControlPopup, temporary:=True)
objMenu.Caption = "&" & Name
End With
With Application.CommandBars("Worksheet Menu Bar").Controls(Name).Controls
Set objMenu = .Add(Type:=msoControlButton, temporary:=True)
objMenu.Caption = "&Absorption Schedule"
objMenu.OnAction = "ViewAbsorption"
Next Y
I have opened an Excel file (map1.xls) and changed some little things in that file.
after that I open a new Excel file with some VBA code in it.
In ThisWorkbook I have this ...
This is the data:
jan09 feb09 mar09 .....
Division 1
Division 2
Division 3
Division 4
Division 5
jan08 feb08 mar08 .....
Division 1
Division 2
Division 3
Division 4
Division 5
The graph would be a bar graph with 2 bars next to each other per month. each bar would be stacked by division. The goal is to compare for example sales in 08 with sales in 09 showing 2 bars next to each other split by division.
I am using the simple conditional format "Data Bar" to make a progress bar, going from 0-1 (0-100%), works fine .5 is halfway...ect. but what i would like it to do is to change to a different colour only when it reaches the 100% or 1 in this case so you can see quickly that this progress is complete.
View 7 Replies View Related