I am looking to return text based on the maximum value in a table.
Dog
2
Cat
10
Horse
4
Fish
27
Lion
1
Let's call the column with the animals over it "A" and the one with the numbers "B", and the rows are 1-5 to make explanations easier
I am looking to have a formula return the name of the animal with the highest three numbers (fish, cat, horse), but don't need the numbers. I am pretty confident this would be an index match formula, but I'm not too familiar with those functions.
I am trying to basically search for the last non zero entry in a column and then return a value in the same row as that last non zero entry.
For example:
My first column of the table is time, second column is submission/marking of coursework (denoted by +1 & -1) and the third column is the number of courseworks awaiting to be marked. My aim is to search the third column for the last non zero value then return the time (from first column) that last non zero value appears.
I am building a table that assigns three roles to chores: Accountable, Helper1, and Helper2. I have a "user interface" tab, where there is a dropdown list where particular chores can be selected. I am looking to have the people in each role for the selected chore returned into the cells. I have attached an example file to clarification. For example, I am looking to have the name (Mike, Shannon, Tom, Blake, Kelsey, Sam) from tab #2 populated into the related cells C3, D3, and E3 on tab#1. I think it's possible with a long imbedded IF function, but I am looking for a more simple solution if possible.
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need Art no. Product Name Brand Group Category Subcategory y-123 abc lux hair care shampoo hair shine shampoo A-123 bca loreal skin care anti acne anto acne soap
Main file Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales y-123 abc lux 120 110 105 A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory y-123 abc lux hair care shampoo hair shine shampoo
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes.
A B C E F G M S Get value from G M S 5G M T M T 6M M U M U 7H M V M V 8K
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
I have a list of projects and a list of milestones and I want to extract every milestone for a project one by one. I have found a way to do this for one project but as there is not a fixed number of milestones, it's proving tricky to do it for multiple projects as I don't know how to tell the formula to stop looking at the Project 1 and move on to Project 3 for example.
This is a very very simplified version of my table (there are other columns inbetween and many more projects)
I have a set of cells and i want it to return all of the distinct or unique values within that matrix. for example, lets say this table charts how many sales an employee makes in a week.
sun mon tues weds thurs fri sat john doe 1 0 3 2 3 0 0 jane doe 0 8 0 1 0 7 0 james doe 3 3 2 3 2 1 1
the desired output would be: 0,1,2,3,7,8
I don't want to be copying all of the cells or doing a lot of manipulation - i know i can copy, sort, and get unique values that way - i want a formula where as i plug in values, this field is updated to include all of the distinct or unique values.
I have a pretty large table,(153,000 rows) My lookup_value is a 7 digit number. If my lookup_value isn't in the lookup_vector, it should return "#N/A" (which it does most of the time) but I noticed it was returning a result when the lookup_value didn't exist...it didn't exist anywhere in the table.
I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:
Payment Status 05/08/2012 10:30:12 INV 05/17/2012 08:27:37 INV and so on...................
The code below (ran from Excel vba) deletes all data from an Access table, paste data from Excel into another table, and then run two Access Macro's and paste data from yet another table back into Excel.
My problem is that when I run the query from Excel, the data returned to Excel is always zero. When I step through the code, and check the Access database, all the steps are carried out properly. E.g, the delete query is deleting all the records in the correct table, the append query is copying the correct data into the correct table in Access. The two macros do run, but then when the resulting table is returned to Excel, it brings back zero's.
Doing all this manually in Access is returning the data correctly.
The two Access macros are running some queries, E.g. the "001 UpdateLatestData" is running two queries and the "002 CreateMonthlyResults" is running about 15 separte queries.
Sub OPENACCESSTABLE_DELETE_ROWS1() Dim db As Object Dim accApp As Object
Set oApp = CreateObject("Access.Application") oApp.Visible = True oApp.OpenCurrentDatabase DBPath 'Delete Query DoCmd.RunSQL "DELETE [001 non motor data].* FROM [001 non motor data]"
Set accApp = GetObject(Access_DB) .........................
Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
(the |'s represent cell borders, if it isn't obvious)
What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4.
I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.
This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.
I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.
I am compiling data from several different, variable, worksheets. I am using concatenate to add the name of each worksheet into a formula to pull the appropriate data.
However, it is returning the text of the formula instead of the results of the formula. How can I make it calculate the result instead of simply displaying the formula text?
Store 1 Store 2 Store 3 Store 4 Top Selling Second Third Fourth
[code]....
I am trying to find two formulas:
Formula 1: Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.
Formula 2 : Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.
I have been trying a few formulas to return numbers from a string of text. The problem I am having is that there are more than one set of numbers that I need returned.
Here is an example of the text entered in F2
"K2PT bus structure [2 locations] & strain structure South West of L9L12-12 [1 location] / Use #4/0, std, cu conductor, connector Cat ID#10842 at structure leg & connector Cat ID#9655 at #4/0, std, cu gnd loop to connect #4/0, std, cu gnd loop to str leg."
What I need returned in a cell H2 is the numbers beside the Cat ID#
10842, 9655
I tried a Return Right Value formula, but I think because there are more than one possible value it isn't working properly.
I have a table of data- IDDescriptionDatePax Count 316COACH01/07/20135 323COACH3/07/201410 324COACH18/07/20141 325COACH20/08/201420 333COACH21/09/201445 345COACH22/09/20143 351COACH23/09/20143 362COACH21/10/20142 371COACH22/11/20142 394COACH23/12/20140 416COACH23/12/201418 422COACH23/12/201439
Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)
This would feed into a second, ID specific table, like this-
I am trying to model emergence timing of eggs based on cumulative temperature units. When the eggs (Column L) deposited on a given date have accumulated 1600 tu's (a running total of Column N) I wish to return the original number (Column L) to a column (Column Q) in Excel. I have also tried returning the date at which cumulative tu's reach 1600 with dget. Unfortunately, I can't figure out how to use Dget or some other function to deal with the relative sums for each value and step through to the next value when the first is returned.
I can get the first number by entering the following formula into Column Q, =IF(SUM(N$9:N18)>1600,M$9,0) but I don't know how to increment it once the first value is returned.
I am having a bit of a problem writing a formula that works under the specific conditions that I need.
I have a range of 6 cells, A1:A6 which contain text I have formatting that turns that row red based on specific conditions. The problem is that if A1:A6 is red, I then need the text in those cells to be copied to Sheet 2 B1:B6.
I have a spreadsheet with 2 worksheets within it. These are simply called sheet 1 and sheet 2.
On sheet 2 I have cell A1 containing =(TODAY()) Also on Sheet 2 I have cells A28 to A39 containing Month names and cells B28 to B39 containing values. Like this
January 1000.00 February 1000.00 March 2500.00 April 1250.00 May 1750.00 June 2200.00
etc
How can I return the sum of these values into Sheet 1 B18 based on the date. That is to say that I want to add up the sum of Jan, Feb, Mar, Apr, May up to and including callcualtaions made on 31st May but as soon as the date goes past 1st Jun I'd want to include June as well and so on.
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
Take the displayed date from a cell, in this case formatted as yyyymmdd, and simply display the date as plain text, not converted to the 1900 date system. For example, I have a date cell that reads 20080610, but everything I have tried returns 39609. But I want it to return 20080610 in plain text so that I can use RIGHT and LEFT functions to extract the year, month and day.
