Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

3
votes
1answer
1k views

Toggled Hyperlink with If:Then formula [duplicate]

I'm attempting to make a formula that will toggle a hyperlink on or off depending on the argument. Basically, I want to make it argue the following: If there is text in (Tab A, Cell A), then create a ...
-1
votes
1answer
33 views

Which formula should I use to place a user ID in one column based on a name in another column? [closed]

Edited to reformat: I have 530 rows of employees in a spreadsheet, each has their manager listed in column H. There are 15 managers. In Column I, I need to assign an auditor to review each employee. ...
0
votes
1answer
175 views

Search Excel price grid with variable values

WIDTH DROP 1150 1550 1850 2160 2360 2750 3000 3480 3980 4450 1200 338 348 378 413 431 450 467 513 ...
-1
votes
1answer
112 views

How to split up above single cell Address value into multiple cell value through macro

How to split up the below single cell value into multiple cell value through macro ? So, this... A1 = MISS FirstName LastName***95*SPECIAL ROAD**LONDON SW92**SW929AB ...should split as below, B1 ...
0
votes
1answer
3k views

Excel formula to sum all entries in a row if value of adjacent cell is equal to third cell [duplicate]

I need to build a formula that will sum the numbers in a series of cells only if the value of an adjacent cell is equal to the value of a third cell. The latter two values are text. In plain ...
0
votes
2answers
82 views

Excel 2007 IF AND function calculation not copying across

I'm using an IF AND function, which is: ...
0
votes
1answer
245 views

How to lock a cell with formula in excel after the referred cell is filled?

I'm using "CONCATENATE" formula in excel and I would like to lock (or turn the formula into value in) the cell which contains the formula after the last referred cell is filled. Some of the referred ...
0
votes
1answer
140 views

SUMIF with multiple match criteria in Excel

I'm trying to create a formula so that if in cell E2 it says "Market", "Limit", or "Stop" it adds the numbers in cells L2 and M2. =SUMIF(E2, "Market" "Limit" "Stop", L2+M2) These expressions are ...
0
votes
2answers
1k views

Nneed to calculate average of NON sequential multiple cells & I want to exclude values = 0 or Empty Cell with AVERAGEIF

Nneed to calculate average of NON sequential multiple cells and I want to exclude values = 0 or Empty Cell with AVERAGEIFS. There is a mistake in my formula but I can't find a way to have it since ...
1
vote
2answers
9k views

Excel compare two columns and list a third column

I have an Excel sheet which has 3 Columns: A B C D 1 A 1 2 B 3 3 C 4 6 5 D 7 E I want to check column C for values present in column A and list corresponding values of ...
0
votes
1answer
231 views

Conditional formatting in Excel 2013 based on inequality between two cells

This is part of a problem for college for which I can't find a solution. Use conditional formatting to format, in blue and bold, the value in column 8 for which the value in column 5 is greater ...
1
vote
1answer
80 views

Averageif with criteria being related to a different cell

I have an array of numbers in excel, and I want to get the average of all numbers where the column headers matches a pattern. Currently, I have data that looks like this: At the bottom I have an ...
-2
votes
3answers
66 views

How to decrease the number of sheets in a workbook [closed]

In a new Microsoft Excel workbook (2010) I have three sheets by default. Suppose that I suddenly press the Insert worksheet button next to the Sheet3, so the forth sheet will become added to the list ...
3
votes
2answers
195 views

Sum of N-highest number in a series excluding duplicates

I had gone through the thread Sum up to N-highest value of series and it's not exactly what I need. e.g. =SUM(LARGE(Range;1); LARGE(Range;2); …; LARGE(Range;N)) The question is if the data set has ...
-3
votes
1answer
140 views

Excel - sumproduct/sumif

I am able to look up single values that meet multiple criterion; but I would like to do more, I want to sum up values that meet two conditions - year and country like in my example. I tried it in my ...
1
vote
1answer
39 views

Formula that looks at value in a cell and depending on what the value is

I'm using Excel 2010 I have a number in cell A1. In Cell B1 i need a formula that says if the number in cell A1 is 25 then put 'account1', if the number in cell A1 is 58 then put 'account2', if the ...
1
vote
1answer
361 views

Excel - Change cell colour based on a date in the comment or mixed in with text

I am using excel 2010 and I am trying to set up a rule that will change the colour of a cell based on a date within that cell. For example the cell may contain Site meeting - 11.05.14 I would like ...
-1
votes
2answers
10k views

Excel - comparison of text between two columns [closed]

How do I compare two or more columns in an Excel worksheet to identify the difference. The comparison of two columns is related to text and not the numbers. Please advise.
0
votes
5answers
1k views

Excel COUNTIF Slow

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...
2
votes
3answers
1k views

Listing non-blank cells in Excel based on adjacent column(s)

Given columns A and B, I want to list the A-values that have a non-blank B-cell in their row, in column C: A B C One Two Two x Four Three Four x ... The best I came up with ...
0
votes
1answer
62 views

Excel 2010 Countif Formula

I'm trying to make a spreadsheet to display stock orders. I am including both executed and unexecuted orders, but I'm trying to display how many orders did or did not execute using Countif. My orders ...
0
votes
2answers
179 views

Google Spreadsheet/Excel filtering

As you can see in this image I have spreadsheet of many athlets, that will participate in competition. My task is using groups (or ranges) DISCIPLINAS (en. Disciplines), which contains 100m run, ...
1
vote
1answer
236 views

How to combine data from multiple sheets into 1 main sheet in calc [duplicate]

I have an excel file which gets generated from various database servers on fly by executing sql. Separate sheet gets generated for each server. Now I simply want to combine data of all sheets. Each ...
0
votes
1answer
80 views

Matching grades to names in separate sheets

I'm having some trouble with an excel formula. What I have in sheet A is a list of student names in (Fname LName) format, and then I have the same list of students in a separate sheet along with exam ...
0
votes
1answer
368 views

Excel formula for tracking tasks and workload on two tabs

I created a spreadsheet at work to track all of the classes taught for us during the year. It helps us with scheduling and it helps us plan our budget for part time instructor payments. We have full ...
2
votes
2answers
156 views

Can I use the Excel Match function to match on more than one type of value

In another question I posed recently I was shown how to use the Excel Match function to query my data in order to produce a summary type table: Creating a formula that queries data in Column B based ...
0
votes
2answers
371 views

In Excel, checking if a cell matches one item from a named constant string list

This question is an expansion on In Excel, how do I check if a cell is in a list of values (a range of cells) . I'm trying to do =COUNTIF(name,G6) and when name refers to some cells, it works fine, ...
0
votes
2answers
83 views

Creating a formula that queries data in Column B based on a selection from Column A in Microsoft Excel

I am reasonably familiar with Excel formulas but I need to construct a more complex query and am not having much success at the moment. I have simplified what I am trying to achieve for the purpose ...
4
votes
2answers
4k views

How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula: =GoogleFinance("GOOG", "price") to download the latest price of a stock. Is there an equivalent function in Excel 2013? Earlier versions ...
0
votes
2answers
251 views

Excel Count number of cells based on three dependent selects [duplicate]

I am looking to count the number of cells based on three dependent select statements. each select corresponds to a column. For example, Select nth-Day (Column A) Select Issue Type (Column B) ...
1
vote
2answers
377 views

Excel SUM function for whole column?

How can I sum the whole column without freeze head? I have a situation: I need to SUM each column, but the total rows in each column will keep changing. users are keep adding data in each columns, ...
0
votes
2answers
2k views

Excel Convert XML cell to columns

I have an excel spreadsheet with XML in a single column. I know that you can import XML data into Excel; but only imports the entire file. However, I need to do this on a cell by cell basis. With ...
-2
votes
2answers
75 views

identify the rows with the lowest values in an array based on identical values in a column of that array

I have an array of product data which includes code, description and cost. code is unique, cost is numeric and description is non-unique. Please see example. How can I select the cheapest of each ...
0
votes
1answer
142 views

I want to map rows from Sheet one on and excel file to other sheets if they meet certain criteria

I work in sales and cover a large territory. To manage the territory, I break it up into "Zones" (i.e. Philly / Pittsburgh / etc). My organization provides an excel file with every company I call on ...
3
votes
3answers
717 views

Custom user function without using VBA

Is it possible to create a custom user function in Excel without using VBA? My problem is I have a long string of standard excel functions strung together to make one very long function. This function ...
1
vote
2answers
259 views

Vlookup across multiple columns

Within one workbook, I have two spreadsheets. Sheet A ("Master") looks like the following - it shows a main branch with it sub branches. Sheet B ("Query") looks like the following: The ...
0
votes
2answers
44 views

Excel: Applying Condittional formatting to every other row

I would like to apply a specific conditional format to every second row in my spreadsheet. In my specific case I have regression results, where the first row indicates the beta coefficient and the ...
0
votes
2answers
86 views

Excel: deleting duplicate records based on an int value

In a sheet that has two columns, the first represents a string and the second represents some field value associated to that string as shown below. I roughly have 30,000 rows like that. My Question ...
1
vote
1answer
462 views

Microsoft Excel lookup match without sorting data

Have a value list and need to insert points by looking up the data table. Data table has value for points in 1000 increments. Actual value that I lookup will not be rounded to 1000 so I tried using ...
0
votes
3answers
1k views

In Excel need to copy a range of cells based on single criteria

I am trying to copy/insert a range of cells based on a single criteria. I have tried using the IF function as follows =IF(B6="RHH1",Sheet2!A4:J15,Sheet2!A18) The FALSE statement works and the ...
1
vote
2answers
3k views

Find last instance of a character in string and keep anything that comes before

I have the following data and I need to get rid of the numbers. This means I will have to find that last occurence of the character " " and keep everything that comes before however I dont know how to ...
0
votes
2answers
146 views

Excel if/then formula

I am wondering if there is an if/then formula for the following situation. I am trying to populate a monthly calendar. I want the calendar to show the value in A1 if the value in B1:B100 is equal to a ...
0
votes
1answer
40 views

Extracting multiple numerical values from mixed characters in excel worksheet

I have a column containing cells with two numerical values, separated by an asterix... e.g. 12.5*3.4 How can i extract the smaller number of the two into one new column, and the larger of the two ...
0
votes
1answer
318 views

=VLOOKUP() function performed on dollar value displays value without “$”

I'm not entirely sure how to present pleasant-looking spreadsheet representations on SE, so I would appreciate any advice on that issue given on the side. I want the B2 cell to display the salary ...
1
vote
1answer
347 views

Excel - How do I make the text in a cell flow over into the cell below

I want the text in a cell to flow vertically, that is, flow over into the cell below. When writing in a simple text that is longer than the column width the text flows over automatically into the ...
0
votes
2answers
46 views

Two columns (pounds, kilos) convert and fill opposite column depending on which data is entered.

I'm trying to keep two columns of data. One in Pounds and one in Kilos. (1 kilo = 2.204 lbs) The problem is that when I add a row, it might be in kilos, it might be in pounds. Is there a formula I ...
1
vote
1answer
65 views

Countifs array with number of days

I'm looking to see how many days people are around between certain blocks. e.g. with people and the days they are around for listed in the first table (A1:C5) and the blocks in the second table ...
0
votes
2answers
5k views

Cannot delete created excel table

I have created an excel table. However, I now want to delete or change it because my range changed. However I can't delete it in the excel Name Manager: Any suggestion why I cannot delete it? ...
0
votes
2answers
33 views

Understanding IF() statement

I am trying to update a spreadsheet that was created by an employee that is no longer with the company. I have been successful in decoding and update all but 1 formula. ROUND(C6,0)*IF($D$3="Y", 42, ...
1
vote
2answers
425 views

Excel Lookup adds double quotes to string

I'm using this formula to get value form a table =LOOKUP(C11;Alias[A];Alias[B]) where C11 = 12354567 But when I use =LOOKUP(LEFT(C11,7);Alias[A];Alias[B]) where C11 = 12345678, the formula does ...