I am building a dashboard for a project, which is going to be on a separate worksheet for easy printing and providing only needed data from my database. I have attached a small example in the format I will actually be using as I think the formula is going to be specific to my dashboard.
Attached file, you should see a column "Rank" highlighted orange, with certain rows being numbered. My objective is to display certain data from that particular row, and display it (scroll to the right to see the dashboard display layout) on the dashboard. I think my main goal is to have the specified data displayed in ranked order 1-10 from the top down, and I don't know where to start with that....the other possiblity is to just have the specified data display in ascending order of the zip codes as long as something other than <blank> is in the "Rank" field.
I have a large table of data which is currently just in alphebetical order. I also have a blank template of the exact table format on another sheet in the same document.
I am trying to create a macro which will pull everything into the current blank table but ranks each row by the overall score (%).
The situation requires some context. I have a spreadsheet that is pulling data in from another location. It is simply a 'Location!A1' link - the source data updates daily as it is a Business Objects report that I dump out and I want to then bring that info into another spreadsheet.
My challenge is attempting to order the data as it comes in without having to manually sort the Business Objects report everyday. Because the link to the BO report is simply a cell look up, I have to take the data as it comes.
I've tried the rank formula according to a price (as this is what it needs to be ranked by) but because some products are the same price as each other, it simply doubles up (so if there's two products at $10, it ranks them both the same e.g. 4). Once I get the right rank, I can then vlookup the right order in a way I can then reference properly. EXAMPLE 180814.xlsx
here i have results from a race. the racers have numbers in the format of a20,c51 etc then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
I got a complicated macro that I need some help on. I want to write a macro that does the following (for clarity, I have attached an excel file).
I want to write a macro that automatically copies the rows in worksheet 'boekhouding' to their corresponding worksheet. The name of the worksheet, to which the data of each row has to be copied, is specified in row I (Beware: if it is not in row I, it is in row H). The names of all destination worksheets are given in the blue cells in worksheet ‘rekeningen’.
For example, row 2 (of worksheet 'boekhouding') should be copied to worksheet 'ABD'. Row 3 (of worksheet 'boekhouding') sheet be copied to sheet 'bestuur'. Row 4 (of worksheet 'boekhouding') should be copied to sheet 'Commissiekosten gala'. Etc for every datapoint (row) in worksheet 'boekhouding'. The number of datapoints will exceed 300, so manually doing this will be too time-consuming and unreliable.
I have one last question. is there a way to make this a little more complicated? for every two possible combinations of names, i have a value. is it possible to create a fourth colum, in which the sum from the three values is calculated?
for example-
12 different letter names (a-l) after running the code derk sent, i now have a-l in cells A1:A12 I also have every combination of 3 using these 12 names in columns C D and E
take for instance the combination of three names (a, b, c). i have values for ab, ac, and bc in columns G, H and R respectively. can these values be summed together and averaged in the fourth column?
I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
Ext P$ Rank Should be 2,128.34 1 1
[Code]...
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
I have a list of data where some of the cell values are the same. I want to rank them uniquely but when I use the RANK function the cell values that are the same are given the equivalent rank. Is there any way of assigning the equivalent values a sequential ranking that is unique?
So in this example rather than producing the rank as this:
i am using this formula with military ranks as the primary value to look up. i know i am doing it correctly because it works with looking up values in another excel workbook when the rank is CPT, PFC, PV2, SPC, SGT, SSG, etc... but it does not work for any rank that is abbreviated with a number as the first character, such as 1LT, 2LT, or 1SG. instead i get a #N/A, which is false. i have looked all over the internet to include this website but cannot find an answer.
I have a sheet diplaying 5 columns of percentages which I need to rank in descending order, i.e. assign rank 1 to the largest percentage and 5 to the lowest. See the attached sheet.
I'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
I'm currently using the formula in cell I43: =AVERAGE(IF(I10:I420,I10:I42,""))
If any of these cells are blank I get the #DIV/0! error. If any are filled in then I get an average which is what I want. The problem is that I am trying to average mutiple sheets of these cells (I43) and if any sheet has this has the #div! error it won't calculate.
I have divided my data into 2 sheets. They are called 1) Sheet1 2)Sheet2
Sheet1 -> Sheet with all my Data Sheet2 -> Sheet with calculations (formula's)
Know what I need to do is the following
1) As soon as someone starts enters a value in Sheet1 (eg. as soon as data is entered in Column A in Sheet1) VBA captures that line # and then in Sheet2 it copies the formula from the ROW ABOVE itself and then inserts a new ROW (corresponding to same row where value was entered in Sheet1) and copies that formula into that row (FORMULA are from COLUMNS B to J.
I need is Sheet2 to update itself and perform calculations when new line of data is started in Sheet1 and to INSERT a new Row so ALL other calculations BELOW it are updated as well. (Eg. Averages, Additional Calculations)
I have a lot of calculations in Sheet2 that correspond to Sheet1. The new ROW in Sheet2 has to look at where the data in Sheet1 was entered because I have a bunch of Averages at the bottom of Sheet2. I need all that to automatically update. So VBA has to insert a NEW row and then copy the previous row's formula in order for that to work.
e.g. Column A shows the item code. Column B shows the age of stock items as 1, 2 or 3 (years). Column C has the cost of the stock items. I want to total the costs for stock items (column C) according to age, so that the totals at the bottom would look something like: Total stock value for year 1: $14,537.10 Total stock value for year 2: $7,657.70 Total stock value for year 3: $1,773.00
This is a complicated thing I'm trying to accomplish and I only have a rudimentary grasp of Excel formulas and logical statements.
I need to check BE2 if it's blank. If it's not blank, leave it alone. If it is blank, I need to check S2. If S2 contains the name of 1 of 11 counties (ex.: "Marion","Pinellas","Orange","Osceola", etc.), I need BE2 to say "TBD". If it does not say one of those 11 counties, it needs to say "N/A".
I am creating a spreadsheet where I am able to track the events that our clients have been invited to AND in a separate column, I need to track who actually attended in order to get a total of how much we have allocated on each client, AND how much we actually spent, to date, on the client.
Through the help of some ExcelForum experts, I was told a pivot table would be my best bet.
Please see attached for an example of the columns on my current spreadsheet.
I need to give each event a value, so if “Jim” Says “Yes” to Golf, “yes” will equal “$400.00”. But if Jim says Yes to the Golf ($400.00), Spring Event ($100), and Executive retreat ($1400.00)” The “Yes” in those three columns will be calculated in a side column ($400+$100+$1400). Which means that the “Yes” must have a different value assigned to it, depending on which column it falls under.
The other important functions that my spreadsheet should have are as follows:
1) I need to be able to manually enter an amount, if need be, instead of a “yes’, and have the total be calculated all the same, in the total column.
2) As mentioned, I need to be able to have one column that keeps the total of how much we have allocated on that specific client (if we invite them to 4 events, and the total of the four =$2000.00, that would be our allocated amount)… but I need a separate column that holds the amount spent on the client to date (If we have “Yes” next to Golf, spa, and trip, meaning we have invited him for the three events and are prepared to spend $1000 on him, but he only accepted to the Golf, I need one column to keep the total $1000.00 and a side column to now read “250”, for example. And if in a few months he accepts to the Trip, I need to be able to track that he accepted, and the “Total Spent” should now be “750” while the “total Allocated amount” should still be $1000.00.
Is it possible to create a pivot table that meets my needs?
I built a excel file to make some calculations for forex. I started with four prices for each day (open, high, low, close). I am calculating for four price ranges for the next day. That is ok. Another part of the sheet compares results to the predictions. From the four prices that come in everyday I calculate average over 8 weeks, over the last week, and individually for each day of the week. Here it is: TESTBook1.xlsx
I wanted to make a surface graph, but I have no table. I have no formula to use to make a table because the calculations are all over the sheet. I tried tracking through the cell where my "total" is put out, but it is a very long and involved trip. Even if I started a new sheet just to make a table, I can't get my head around the calculations.
I have two cells where I put in my adjustment numbers. In the first cell I put in a number from 0.00000 to at most 0.30000. The second cell I put in a number from 0.00000 to at most -0.30000. I then look at a third cell for the highest output. I got tired of typing in numbers, so I put sliders which works better.
So my table would have 0.00000 to 0.30000 on one axis and 0.00000 to -0.30000 on the other. The formula = ????
I'm in the process of decoding a mass amount of data that will be incoming shortly. The data will be formatted as shown below. What I would like to do is have a macro that between two full seconds (for example 10:11:48:0 to 10:11:49:0) would find the mode of the RSSI values, paste that with a time value that is in the general excel format. (I.E. 10:11:48 = 0.42484). When all is said and done, what I have access to should look like the second string of posted data. I can get the time cleaned up on my own, but I'm having trouble with the loop macro that will find which cells to use for the mode.
I'm trying to create a macro that allows a date to be entered into a Dialog box that is activated by a short cut key.
The macro would use this date to look through all the information on tab Output, and if(as) it finds dates that match the information will then be filled out on tab Schedule.
For clarification Work Center would be the abbreviation (letter code) about the dates.
i have a spreadsheet that has 300+rows,and the details below
b2 is the description and date, b4 to b29 are the item name c3 is total sales volume between c4 to c29, then another section starts from row 30 till row 57, row 58 till row 85 etc till the end (27 rows for each section)
i need to search the minimum value in each section and highlight it as red.
i know i can search the minimum value by using MIN(b2:B29), but how can i repeat it for each section?
Currently I have developed a Time sheet for employees however I am having a lot of trouble with the shift differentials right now.
First, I used an If function to say If(B7="E",B6,0) E would be the evening shift and of course this works fine if everyone worked an perfect Evening shift within the time limits however, they dont.
I'm wonderin if it is possible to have one, or multiple formulas that can do the following:
First shift differential is from 14:00-23:00 Second shift differential is from 22:00-07:00, However lets say you start at 14:00 I don't want it calculating the shift differential of the first one for 22:00-23:00. Third shift differential is a weekend one which I have figured out it's rather simple to just have a IF function for that.
Start time is B3 End time is B4 Breaks is B5 (However, Breaks is subtracted from B6) Hours worked for the day is B6
How could I do a lookup on the following accounts but I want to ignore all the numbers and spaces. For example, in another sheet, I just have ACCRUED PTO and I want to do a vlookup on 601190 601190 5007 ACCRUED PTO.
I'm currently working on a side project for work that I believe will save many man hours as at the moment we're using paper and calculators!!! I'm scheduling buys. I've worked out formulas and formats to show how much we should buy and how they should be split (for example for UK purchases we buy x amount and split it 10% 90%). However, the dates of delivery are different. See below:
PORT L'poolPORT HKPORT Portsmouth 1.113-Aug-1206-Aug-1230-Jul-12 1.210-Sep-1203-Sep-1227-Aug-12 2.103-Sep-1227-Aug-1220-Aug-12 2.201-Oct-1224-Sep-1217-Sep-12 3.124-Sep-1217-Sep-1210-Sep-12 3.222-Oct-1215-Oct-1208-Oct-12
Campaign 1 first buy (1.1) I know that my product is sailing from port HK. Therefore I want it that in the 'delivery date' cell, it returns 6/aug/12 (that's the first lot of 10%, and underneath it returns 3/sep/12) which the remaining 90%...2.2).
However, both the campaign number AND port options are variable (using a data validation drop down list). In effect meaning if I choose campaign 2 instead of campaign one, it'll return the dates 27/aug and 24 sep....if I then change the port to Portsmouth, the dates will be 20 aug and 17 sep.
I've attached a quick view of the schedule I've created. The yellow cells are where I want the dates to be returned.
I'm trying to use a state transition matrix in Excel. I randomly generate a percentage and want to lookup the value in a corresponding table where this value would fall if we were to sum across the row. For example, the first randomly generated value is 13.5% and we are starting in state 6. Therefore, I want a formula that looks in row 6 (starting state) and determines which column (1-6) has the value 13.5% if you sum the values across (in this case the answer is 5). Then 5 becomes the new starting state and so on. Is there a formula or combination of formulas in Excel that can carry out this complex function?
State transition matrix 123456 185.00%10.50%3.10%0.74%0.66%0.00% 28.90%79.00%9.20%1.70%1.20%0.00% 31.00%7.50%73.00%15.00%2.50%1.00% 40.00%1.00%13.00%76.00%9.00%1.00% 50.00%2.00%5.00%6.00%71.00%16.00% 60.00%1.00%1.00%6.00%9.75%82.00%
I have a master table that has a list of Product Names in column A and the associated product rollup names in column B.
Next, I have a data table that has a list of company names, Product Names (the names are all included in the master list of Product Names), Transaction Type (Sale, Cancel) and Value.
Lastly, I have a data summary template that I built where I want to be able to calculate total values based on the data table and the product rollup from the master table.
I would like to build formulas into the summary template where it looks at the Product Name column from the data table and finds the associated Product Rollup from the master table and then sums all the values for Transaction Type Sale and then sums up all the values for Transaction Type Cancel. The end result would be a Sales Value for Product Rollup 1, a Sales Value for Product Rollup 2, a Cancel Value for Product Rollup 1, a Cancel Value for Product Rollup 2, etc.
