My company uses financial analysis software to evaluate data on a monthly basis. The software has a report writing package that is similar (but not exactly like) Excel. There are 2 functions of this report writer that I would like to find out if
Assume A1=4 and B1=5 (but the data may vary in the future). In C1, I want an green up arrow if B1>A1. If B1
We are running Office 2010 part of Office Professional Plus 1010 (corporate install). We have added both the Analysis Toolpak and Analysis Toolpak - VBA to the add-Ins and have the button on the toolbar, however when we click on the button, we don't get the popup window allowing the user to pick the analysis tool they want to use. Have tried this on a number of PCs (we all run the same version) and it doesn't work on any of them.
What we have to do do make this toolpak work?
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.
can I put formula to cells on relative basis?
For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?
Excel 2010. I set up a trial balance as following:
1/31/2000 2/28/2000 3/31/2000 ------- 12/31/2012
Acct # 1
Acct # 2
Acct # 3
Acct # 4
I was able to use pivot table to set up the balance sheet and income statement.
1. now each month is a field which I can choose to add to the report, but the field list is getting too long (and growing). Is there a way I can insert 1 field (for the user to input the exact date) or 2 fields (to input year and month) and the pivot table can pick up the input and refresh either automatically or manually.
2. I looked into the "group" function in pivot table, but how do I group the above data in quarterly and yearly fashion so the user can get those information quickly?
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
I'm using Excel 2010 for Mac.
Here's my problem: I need to keep two values subject to two upper limits (i.e: value 1 < x, value 2 < y).
These two values are equations that are dependant on 3 variables. The relationship between these variables are not linear (i.e.: can't just plot a graph and mark where the lines cross).
Is there any computational method using Excel I can use that will give me the LOWEST value for the three variables in order for my target values to be under their upper limits?
While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre
=SUMPRODUCT((TEXT($A$2:$A$14,"mmm")=$E3)*($B$2:$B$14=F$2)*$C$2:$C$14)
See the attached file to get more idea of my question. Then I would Like to Highlight Entire Rows which contains a Specific text.
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.
View 7 Replies View RelatedI am trying co Calculate number of nights between to dates for example 10/26/2012 -- 11/25/2013 in a period that can be greater than the month Period. for example. some One checked in on October 24, 2013 and they will checkout on december 17, 2013. in the monthly Period of 10/26/2012 -- 11/25/2013, I am trying to calculate, the monthly nights of that period itself, and the number of days he was checked in on that period.
View 1 Replies View RelatedWe know that professional emails have a fixed format. I have a list of people with their names and emails and there are some people for which emails are missing. I am pasting my sample data below. But first let me explain the meaning of columns.
Column A: Means full name of the person for which we have email id
Column B & C: just split of Available full name into first name and last name
Column D: Email id of the person with full name in column A
Column E: For the names in this column, we need to get email ids
Column H: Sample result
Now basis on the Full Name(Column A) and Email Id(Column D) can we get the email id for person in Column E.
Sheet4
A
B
C
D
E
F
G
H
1
Available Full Name
Available FirstName
Available LastName
Email for available full name
Actual Full name
Actual firstname
Actual lastname
Sample result
[Code] .........
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
View 3 Replies View RelatedBelow are the weekly closing prices for a stock market index. I'm looking to use conditional formatting to highlight in green "buy" signals and in red "sell" signals.
Buy Signal: If the index rises 4% above any previous low point.
Sell Signal: If the index falls 4% from any previous high point.
DateAdj Close
3/10/201472.16
3/3/201473.55
2/24/201472.83
2/18/201471.92
2/10/201471.43
[code].....
In the above example a Buy Signal is generated on 1/13/2014 because the market closes 4% above the low of 68.24 on 12/9/2013.
[URL]
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:....
I have an excel 2007 script that downloads daily stock data and prices. I do analysis and graph the data.
I would like to keep the download the same, but modify my analysis so as to obtain weekly and/or monthly data.
I am sure this is commonly done, and is not rocket science, but so far I am baffled as to the procedure / algorithm to do this.
I work on graphics which show financial data. The base is day data together with calculated added values the graphic worked and showed good pictures.
But now I encountered a problem with the graph - related to not listed days, points are "generated" which do not be in line with the rest of the data !?
EXCEL_Forum_20140118.jpg
I'm researching a load of financial data downloaded for me from Bloomberg. It lists daily prices of 15 commodities for nearly 20 years in some cases! Its far too much data to get anything worthwhile out of, so I'd like to know if any of you guys have used or know what the VBA is to write a macro that will reduce all the data from daily to monthly. (ie. I want a macro that will allow me to quickly delete all data corresponding to dates which are NOT the end of the month).
View 5 Replies View RelatedI have a sheet with following columns
Zones Month Revenue
Zone1 Apr-05 1111
Zone2 Apr-05 2222
Zone3 Apr-05 1222
Zone1 May-05 1451
Zone2 May-05 452
Zone3 May-05 4622
Zone1 Jun-05 891
Zone2 Jun-05 255
Zone3 Jun-05 1555
and so on all zones revenue each month.
I am learning Pivot table and struck at a point.
I am able to do simple pivot table but now want the following result
1. All the three zones in row fields.
2. Months in column field.
3. Revenue and running total of revenue (progressive sum) for all the months of desired financial years. ( I can get revenue and running total separately but I want these in alternate columns ie Revenue Apr-05, Revenue upto Apr-05, Revenue May-05, Revenue upto May-05 and so on)
Also how to get data for desired financial year only as 2005-06 (Apr-05 to March 06) or 06-07 (Apr-06 to Oct-06)?
If I have 2 excel files. One with data about electronic equipment and one with more general counting data, i will explain..
For example the electronic equipment excel file contains data about notebooks, desktops, epads etcetera and also prices and how old they are etcetera. The excel file also has a column for serial number, so for example a notebook serial number looks like this: 23N34ERT3 and an epad SN looks like 25OKE445EE. IF i filter the SN on text and begins with: ??N then it will show me only notebook data(because the N stands for notebook). Same if I only want the Epad I just filter on ??OK, so i get all the epad data.
now for the general counting data file, in this file i actually want to put data which i get from the other electronic equipment file. For example i want this data to be retrieved:
a. total number of rows of notebooks from the electronic equipment file
b. total number of rows of epads from the electronic equipment file
c. how many rows there are for notebook that are 0 - 1 years old(in electronic equipment file there will be a column called product_Years so in this column you have data like: 1,4,12,3) + that are from model: A from the electronic equipment file
d.how many rows there are for notebook that are 2-3 years old + are from model:A from the electronic equipment file and then going on for 3-4 years model:SD etc......
e. in the electronic equipment file there are prices for each model, i also want to calculate the prices for each rows which i get here in the list above.. These rows must be calculated with prices from electronic equipment file
Is there any easier way than constant filtering and copy pasting the data?
creating a macro to send email from excel through Outlook basis of different conditions and with embedded text and subject.
There 2 workbooks attached with this post. The workbook named "Data" contains the data and the workbook named "Emails" contains the Dealer Names (in Column A), the Email ids of the contact person (Column B, C and D), the subject (in Column E) and mail content (in Column F).
I need a Macro which first split the data basis of "Dealer Name" Column F into multiple sheets from "Data" sheet. Now, each sheet will contain only the Individual dealer's data. The data whose ageing is 2 or more than 2 days (in Column H) is highlighted in yellow.
Now, I need to send a separate email to dealers with their data details from range A to F (including headers) as an embedded text with subject and mail content which is present in "Emails" workbook.
Now the twist is, if any dealer having the Aging value in Column H is 2 or more than 2 days (highlighted in yellow color) than the email should be sent in CC to Column D email ids but if there is no Aging more than 2 days than the email should be sent in CC to Column C email ids.
"To" email ids (in Column B), subject (in Column E) and mail body (in Column F) will remain same in both conditions. Column A is containing the Dealer Names through which macro will identify the email ids.
I need to create some trend analysis for Vehicle sales. I have attached a file with sample data.
There are few problems i am facing like description has all the vehicle details which are not in any particular order, even if some how i manage to separate these then too many variables are not letting me come to any conclusion for example make, model, mileage, year of registration color of vehicle, auction sold, sale price, cap (bench mark for pricing).
what i need to produce is which auction sells certain vehicles best or which color of certain model sells best.
[URL]
Using excel 2007 and I noticed some differences between how a caluation is done via PEMDAS (as I understand it) and evaluating vai excel.
The formula is just an example and doesnt relate to anything as such.
The formula is: 2*6+3-4/2-5+20/5*3+50
using [URL] the formula show the calculation as I would have calculated.
Manual Order.png
When using excel it does it in a different order
Excel Order.png
Any reason why?
I am trying to do this preferably not in a macro if possible. Basically I have a monthly metrics spreadsheet that pulls it's values from a data entry sheet. I setup the display sheet so that if there's no data for the month the cell is blank in order to avoid errors. So, now what I want is one cell that will update and tell me if the last month's metric was good or bad. So, I am trying to create a cell formula that will be able to find the last row with data. In this case it would be the row corresponding to September's data, then evaluate the value in that cell to see if it's above or below 90% = 0.90 in this case.
View 6 Replies View RelatedI created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
View 2 Replies View RelatedI have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View RelatedI have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
View 1 Replies View RelatedI'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies View Related
