I'm trying to come up with a formula to find and proper sum the value in a table like this one:
Month/year 1995 1996 1997 1998
Jan 3,63% 0,86% 3,5% 1,5%
[Code] .........
But the actual table goes till december and the years till 2013. For instance, if I need the cumulative index from, let's say, jan/2010 to feb/2013, I'm having trouble with this formula. I can't get excel to sum them properly.
The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)
1 DTX $ 1,292.00
2 DTX $ 1,292.00
3 DTX $ 1,292.00
[code]....
I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.
I need a formula that will allow me to search a column (B1:B19989) that contains text with the data from a column (A1:A32417) that contains a list of part numbers and display a result(True/False) into another column (C). Column B's text has the part numbers mixed in with the text. I need to know which numbers from column A are found within the text of column B.
This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
All I get is #N/A - I know that the value in A1 exists in CATALOG
I have copied and pasted the formula off the ozgrid page using a 3 column table laid out as I believe it should be however I just get the # name? error message what I am not doing right. Please help. I just want to be able to generate a random name from a table of names, eventually I need to amend the formula to encompass a larger table but I can't get the example to work!
Have 10 columns... one column would be for the "1s", another for the "2s", another for the "3s", etc...
But I don't want to have them in straight order (i.e. 1 x 1 =, 1 x 2 =, 1 x 3 =, etc...) because the pattern is easily memorized, no matter what number he's working on.
I'd like to be able to hit a button (or something) and have each column shuffle (or randomize) the order in which the equations will be displayed.
So, a column might come up 3 x 6 =, 3 x 9 =, 3 x 1 =, 3 x 4 =, etc...
And next time it might come up 3 x 5 =, 3 x 2 =, 3 x 4 =, etc...
This way he won't be able to memorize any patterns and he'll actually need to learn his multiplication tables.
I have a table with 500 entries which is some physical properties for some chemicals. What I am needing to do is get something to find out what chemical formula the user has typed in another cell on a separate sheet, then VBA finds the component on this table and picks out information from columns within that row. Some of that data will then be used to do a calculation. The user can select up to 20 components, so VBA needs to be able to do it for 1 or anything in between 20 components. The data which I need to use for further processing is within columns D to J.
I've got a bit of a problem revealing pivot table source data, so just wondering if anyone know how to reveal it? My colleague sent me the spreadsheet, I can see the pivot table, but I can't find the source data that the pivot table links to.
I'm using VLOOKUP with MATCH to find data in a table. I now have to add a third condition to the look up. I've attahced a file as an example. I'm not sure how to list the data for the third condtion. I created another table with the data for third condition.
I have a table with data and I want to transform the data in table 1 to the format in table 2 with a formula. I want to change the table format since I have data with 12 000 rows in table 2 format. I haven't been able to find any formula that works, so I will try to explain my problem
Table 1: How the data looks like
Account number Account Name Description 2013-01 2013-02 2013-03 2013-04 2013-05
4010 Bank Sales 0 10 0 5 0
[Code] ........
Table 2: How I want the data to be transformed
Account number Account Name Description Month Amount
4010 Bank Sales 2013-02 10
[Code] ........
1. The same accounts are used several times, but with different description or months. 2. All "0" values are eliminated in table 2.
My first thought was to use a pivot table, but I can't use it on sheet 1 since each month become a separate value.
Maybe there are some setting in pivot tables that makes it work, but I would prefer a formula to solve the problem.
Need formula to pic required data from Validation,
In the excel sheet Orange color (Data Validation) , If i select the data validation(Like, Jan-Orange- consumption) and corresponding data should display, so i required formula for that.
I have a pivot table, and I would like to be able to have a formula or vba to extract the data to separate sheets. The pivot table looks similar to;
Row Labels Count of FileSemester John Smith 8
[Code].....
The pivot table consists of employee name (John Smith), followed by class code and qty. I have a separate spreadsheet for each employee and what I need to be able to do is to extract the class & qty into these spreadsheets.
I have a spreadsheet where I need the end result to be auto filled after recognition of an adhoc "top up" amount. How do I get E7 (3170) to automatically recognise the insertion of C7 (3000) to give this result without having to manually update the formula in E7?
i have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?
I have 4 names, and a grid of 100 boxes. I need those 4 names randomly deposited in the 100 boxes, making sure each name is displayed an equal number of times (25 each) Is there an Excel formula to compute this?
I need help creating a formula to find data in one column that starts with different letters and counts them up.
For example one column would contain the words amoung others: DPDE DNPD EPRO EOTH YBRC YUND
In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.
The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.
I am creating a "board game" type game in excel and one of the functions will be landing on a cell. When you are on this cell you click the checkbox to the left of it which then flips the value to true. I then have another formula generating a random number between 1 and 100 if the value is true. I would like to make cards in a separate sheet out of single cells but make about 40-50 of them maybe less. I want it to be able to pull a card(cell's) contents into another cell on another sheet. But I want it to be a random card associated with the random number generator. I can definitely make changes if this isn't possible but the point of it is to show the contents of one of the random cards.
My objective is to take two different random counts of numbers written in column b, ranging from B9:B66. However I want to highlight the specific fields listed in the example below, and pull random numbers from those selected fields only. Then I want to pull another set of random numbers in the same column B9:B66, however using a different specific set of fields within column B; as shown in the second random formula string. Use the Press 9 feature to randomly select the two different numbers. What this attempt is trying accommplish is selecting one boy, one girl who have assigned numbers adjacent to their names(name field populated in column c). What am I doing in correctly?
HTML Formula written to B70 =INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52)),1)
Formula written to B72 =INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B10,B11,B12,B14,B18,B19,B22,B24,B26,B27,B28,B29,B30,B32,B36,B40,B43,B46,B48,B49,B53,B54,B55,B56)),1)
I am making a sheet that has many colums of data that I want to sumup colum wise. I am doing is using this macro .. ============================================= Sub Add_Totals()
For Each NumRange In Columns("C").SpecialCells(xlConstants, xlNumbers).Areas
I have created a spreadsheet at work which is being used by a couple of people. One of the worksheets rounds off figures from a table in sheet1. My problem is that once I've set the spreadsheet up it seems to work fine.
But i opened it again and it displayed a random figure but the formula stated "=#N/A". I'm pretty sure all the computers are running the Addin correctly. Is there something I've left out?
I just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
In this file Vehicle Fuel Tracking.xlsm I have a pivot table set up to filter my data. Next to the pivot table I have a column that Totals the Mileage based on the MAX and MIN of each group. I am looking for a dynamic formula to keep the totals alligned if data is added or deleted from the pivot table.
I would also be open to changing the data table to accomodate this request if needed.
