Using Data Tables For Calculating Net Present Value?
Dec 22, 2013
I have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
How to use the data tables function under the what-if analysis.
My sheet and calculations are all set up
But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV.
Ive done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
View 1 Replies
ADVERTISEMENT
Aug 20, 2008
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View 5 Replies
View Related
Apr 5, 2014
I have a daily report that gives a percentage based on what is typed into the "Supplier" and "Total Sales" columns but I'm having trouble writing the following formulas since my excel knowledge is relatively low.
1. Where a cell in the "Supplier" column says anything but "Den", "Burrowed" or "Studio Nyx" I need the "60%" and "40%" columns filled with the relevant formula (=B11*60% for example) and the "100%" column blank.
2. Additionally, if the cell contains "Den", "Burrowed" or "Studio Nyx" I need the "100%" column filled and the "60%" and "40%" blank.
View 6 Replies
View Related
Sep 7, 2013
I'm trying to figure out whether or not something is possible in power pivot. I'd like to load two tables into powerpivot and then set up formulas that create calculated values across the two tables.
Here's a simplified description of what the tables look like. [URL] .........
The pivot table should join the two tables on Month and Category and allow me to create calculated values across the cost category, like this. Note it's filtered to a single cost category right now: [URL] .......
In this view it's filtered to all cost categories but i'd be looking to still maintains the right count of members (i.e. it doesn't inflate the numbers from duplicate records as you might get if you were to join the two tables in a sql database): [URL] ....
The actual tables I would use are quite a bit larger (too large for excel without pp), and have quite a few more of each type of category.
Would something like this be possible to do in powerpivot without having to do some data prep work in another app to join the two tables together?
View 1 Replies
View Related
Aug 17, 2008
i am trying to make a spreadsheet up which will calculate and sort out teams in a league table for my local football team. basically, i have around 6 teams in the league and they need to be sorted via: points, then goal difference, then goals scored and then by the match between the two teams if still equal. I have managed to work out how to get excel to calculate all of the numbers of wins and losses and goals scored, etc but cannot find how to rank the teams in order regarding how i just explained.
View 9 Replies
View Related
Sep 11, 2009
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies
View Related
Aug 28, 2008
how to use COUNTIF to count up the number of times something appears. What I want to do is add up values if an H is present in the cell with the value eg. if 3 cells contained:
H, 3.5
7.5
H, 3
...then 6.5 would be returned.
View 14 Replies
View Related
May 12, 2014
why match turn out NA but data is there ,I'm not very good in this or this formula don't really work,
View 5 Replies
View Related
Mar 14, 2014
What is the simplest way of see if after a filter has been applied with a criteria, whether any data is visible using VBA?
View 2 Replies
View Related
Dec 28, 2008
I have a situation where i have headers in a file but just because there is a header does not mean there is any corresponding data in that col.
I was keying off if just the header was present then i need to do "something" but now i need to key off two things:
1) Header present
2) If there is anydata in the col when a header is present.
Question: How to use vba to determine (quickly) if there is any data in a given column.
View 4 Replies
View Related
May 14, 2012
So I am trying to write a bit of code that will insert a number in sequence in column A if there is data in column B. I need the column to be numbered 1-### if column B contains any data. I not been able to find this code anywhere, just delete if or count if statements that do not give me what I need. I think I have a few pieces of the code (e.g. Active Cell Offset) but can not seem to fit together in the correct order.
View 3 Replies
View Related
Oct 4, 2006
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
View 8 Replies
View Related
May 28, 2008
Is there a way I can return a column heading(s) if text is present in a row?
Refer the attachment... ozgrid.xls
Names are listed down the left, headings along the top, with data in the corresponding cells. For each unique name i.e 'Steven' is there a way to list each heading data is found i.e 'Shop2, Shop4'???
View 5 Replies
View Related
May 12, 2008
I work on csv files which are created everytime someone makes a change in certain parameters used by a robot, however when opening these in excel they are nonsensical.
This can be changed with a few macros etc. but my manager would like it to be a "press one button and it works" procedure.
So what im looking for is some sort of function that checks if there is any data in the next column of the spreadsheet and runs a macro in that case and only that case.
View 9 Replies
View Related
May 28, 2008
is there a way I can return a column heading(s) if text is present in a row?
refer the attachment...
names are listed down the left, headings along the top, with data in the corresponding cells. For each unique name i.e 'Steven' is there a way to list each heading data is found i.e 'Shop2, Shop4'?
View 4 Replies
View Related
Sep 27, 2006
I've taken over a spreadsheet and been asked to produce various charts. One chart in particular asks for views of the following (hyperthetical) data from different workbooks on the same sheet
July 06 - 24%
June 06 - 22%
May 06 - 29%
April 06 - 21%
overlaid with this data
April 06 - 42
May 06 - 68
June 06 - 47
July 06 - 55
The problem I'm having is that firstly, the dates are presented the opposite way round, and secondly one set of data is in percentages, the other in basic integers. The spreadsheet data is large, and historically has always been done like this so it's not easy to change the way its presented, but is there an easy way to show it in a combination chart?
View 6 Replies
View Related
Jan 11, 2013
I need change the color of the cell depending on the following conditions. (By the way the column will have names list)
1. single punctuations are allowed anywhere in name apart from first position.-->color should not change
2. If any name got consecutive punctuations --> color should change to RED.
3. Any punctuations apart from Dot(.), hyphen(-), apostrophe('), Space( ) are not allowed-->color should change to RED.
Example-
ShivakumaraNo Change
Shiva kumaraNo Change
Shiva'kumaraNo Change
shiva.kumaraNo Change
shiva kumarared
shiva''kumarared
shiva' kumarared
shiva. Kumarared
shiva.'kumarared
shiva:kumarared
View 4 Replies
View Related
Aug 25, 2009
I have a formula that I use to tally data and return the number of times a particular value is present in column C.
IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,E:E),"")
I copy this formula down the sheet until arriving at the last input in column "C".
Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.
View 9 Replies
View Related
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Apr 30, 2013
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
View 8 Replies
View Related
Jun 16, 2014
All i did was i just created a form to open a workbook from the directories.
Code to copy the data from that recently opened workbook to my workbook where I have my forms. I need to copy that data as in the case that i don't know the workbook and the sheet name i'am going to open as i may open any of the files!!
View 2 Replies
View Related
Feb 16, 2010
Column A contains multiple data, work order, account number, account name such as "#12345 555 hello"
I have a list of account numbers and want to see if or not these account numbers are listed in column B.
I tried =IF(ISERROR(SEARCH("555",a1)),"","Yes") and it did work for the one cell, but how can I change the search cell from H30 to an entire row? Also, is there a way to reference column B that has the list of account numbers instead of having to manually type in the 555? So, have it say something like this:
=IF(ISERROR(SEARCH(B1,A1:A30)),"","Yes")
View 9 Replies
View Related
Feb 18, 2009
Let's assume, starting at A1, that i have table (8c x 30r)
and lets assume that in each of the columns i have 5 cells filled in with data at random.
What is the best way for me to produce a list of the data in each of the columns without having to go in and delete out all the blanks myself? I.e. i want to reproduce the table but without any of the cells which do not have any data in.
View 12 Replies
View Related
Jan 25, 2012
I have been trying to find a way to consolidate data in two tables, but can't seem to locate a good response. I have been toying with EXACT, VLOOKUP, HLOOKUP, MATCH, and SUMPRODUCT, but none seem to work for my issue.
Two Files - Call them acrendo.xlsx and office.xlsx
Both have a single sheet and contain first name, last name, and date columns. Office also has other information that I'm trying to merge into the corresponding row of acrendo.
Basically, I am trying to take the combined first name, last name, and date column from a row in acrendo as a single record, look for an exact match in office, and if it finds one, return the corresponding values in Coumns D, E, F back to acrendo. Someone suggested using the following sumproduct formula:
=SUMPRODUCT(--(A2=[ofc.ally.xlsx]ofc.ally!$A$2:$A$9329),--(B2=[ofc.ally.xlsx]ofc.ally!$B$2:$B$9329),--(C2=[ofc.ally.xlsx]ofc.ally!$C$2:$C$9329),E:E)
This always returns a value of 0, even if there is data in the matching row to be copied over.
One small complicating matter is that there may be duplicate entries in office (i.e. one row in acrendo may match to four rows in office). I can cleanse that if necessary, or preferably, if multiple matches are found, it would simply insert the additional rows into the acrendo file.
Also, is there a way to go through the acrendo file and check for duplicate records and delete them (e.g. if there is the same last name, first name, and date, delete the duplicates).
Can't do this manually as I have acrendo has almost 18,000 rows and office has almost 10,000 rows.
View 1 Replies
View Related
Jan 12, 2014
I am trying to merge data from 2 tables into a master sheet. Column goes like this 1 = customer ID, 2 = allocated number, 3 = age
The first table has around 2000 entries and 2nd table has about 1000, and they both contain some overlapping entries. I wonder if there is a simple way to go around this rather than manually looking at each individual entries as I will be more tables to fit in later on.
View 9 Replies
View Related
May 24, 2007
I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?
View 2 Replies
View Related
Jan 21, 2008
I need to check data from two tables(A and B). They are supposed to match each other and my job is to check if there's error in it. Each row contains several criteria, and all criteria need to be matched. I need to give a report on the info that
1) contains in table A but not in B
2) contains in table B but not in A
3) contains in A and B but some of these criteria do not match..
I have basic knowledge of VBA. Forget to say, they are in different order. And even after sorted, the name may still unmatch with each other, so can not use formular like "=Sheet1!A1=Sheet2!A1"
Example:..........
View 3 Replies
View Related
Apr 16, 2013
I have a pivot table i created to pull in data in a tabular format so i can have all of my data fileds listed in a columns. On another page I am doing a dashboard that is using Countifs to pull in data to my report. What I am running into is the countifs are only working when i filter my pivot table to a certain value that matches, otherwise it will not pull the counts in..
View 5 Replies
View Related
Oct 17, 2009
I've encountered is the spreadsheet attached. Basically I am trying to create a "complex" array (at least it's complex for me lol) to basically extract some values from tables and put them in one together. The array I thought would work is on the table with BRIGHT YELLOW BACKGROUND, whereas the table that could do the same work but more "manually" is with a RED border around it. I know I can do it manually as the red border table shows, but I've been trying all day to make it work with a complex array.
View 4 Replies
View Related
Nov 11, 2009
I need to find the most frequent number for each name in a large datatable, eg. Name1=12.
ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..
Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.
View 10 Replies
View Related
