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

learn more… | top users | synonyms (5)

1
vote
1answer
16 views

How to determine largest individual value in excel in a group of identical cells?

I am trying to figure out Column D Which SKU has highest total? how do I do that in excel with a formula? In this case I determined it manually, I tried IF=IF(A2=A3,IF(C2>C3,"Largest","NOT Largest")) ...
0
votes
1answer
18 views

Adding value of rows if column A and B cells are similar in Excel

I have a situation. I have three columns | A | B | C | |Category | Sub | Price | Column A is basically Category column, column B is Sub Category and "C" is price. I need to add the ...
2
votes
2answers
30 views

IF statement formula for overdue DVD rentals

I am creating a spreadsheet for my local parish church to keep track of borrowed DVDs: The DVDs have to be returned 14 days after they are borrowed. If the current date is over 14 days after the ...
0
votes
2answers
31 views

If Difference Higher or Lower than Display

I need a excel formula that will show: * "1" if B is less than A by more than 2 * "0" if B is within 2 of A * "-1" if B is higher than A by more than 2 Can someone please tell me whether this is ...
0
votes
1answer
21 views

Calculate time difference in percentage

I'm trying to make some statistical data on the performance of a specific task. One of the data points gathered is how long time it took to run. I'd then like to compare that against the expected ...
0
votes
1answer
19 views

Excel formula to show multiple cell address or row locations?

I'm using the following formula, which returns a numeric value: =SUMPRODUCT((A3:A176="Thing1")*(B3:B176="Thing2")*(C3:C176="Thing3")*(U3:U176="No")) However, I'd like to also know the cell address ...
0
votes
1answer
34 views

Extract text between emails with < >

Original link Extracting Data Between two Characters in Excel I have a combination of cells where some have nicknames and some without nicknames. Some will have nickname < > emails and same will ...
0
votes
1answer
67 views

How to return all the cells in a row if first column matches a value on another worksheet

I have 2 worksheet tabs in an Excel workbook, tickets and tickets info. I have values in Column A of tickets (from A2:A500). I want to search for each value from A2 to A500 of the ticket sheet looking ...
0
votes
2answers
31 views

Fill a large range with a formula in Excel, without mouse-dragging to extend

I have 5000 elements in column A and 5000 in column B I want the element wise difference and stored in column C. The subtraction command I know is =A1-B1 then drag all the way down with a mouse. ...
0
votes
0answers
21 views

How do I superimpose a conditional formated heatmap based on one set of values over a different set of values in Excel 2013

I would like one cell to be formatted with a gradient color based on the value in another cell. For example, I would like cells A1 and A2 to be highlighted based on the values in B1 and B2. If I ...
0
votes
2answers
25 views

EXCEL VLOOKUP returning #NA when IF condition specifies “ ”

=IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Big'!$B:$B,1,FALSE),"Project BIG",IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) ...
1
vote
1answer
42 views

Excel 2013, SUMIFS formula

I think I need to use a SUMIFS function for this but I am not positive. I have a list of codes: All of these codes are in a category of downtime, each code also has minutes 5,10,15,40,65 etc. My ...
0
votes
2answers
56 views

Excel: transforming an indexed sheet with many columns to two columns

I have an excel sheet with rows like this: Fruit | Apple | Banana | Grape | Peach Vegetable | Cabbage| Lettuce| Carrot| and would like a two column output duplicating the index (first column): ...
0
votes
1answer
15 views

VBA - auto-populate date when data entered in cell

I have a tracking spreadsheet where I'd like the date a cell is completed to auto-populate in the next column. I found the following code which works for the first (I:I), but I'd like to add the same ...
-2
votes
1answer
25 views

Excel 2010 - possible to apply a function to an entire column?

In Excel 2010, is it possible to apply a function (not a formula) to an entire column (without dragging the plus symbol all the way to the bottom)?
1
vote
1answer
26 views

Create a formula to find difference between values in regularly spaced rows

Lets say I have 100 rows in column A. That would be 100 cells with a value in each. I only want the value of the difference between every 5 cells. For example, in the first cell, A5-A1, then next cell ...
0
votes
3answers
35 views

How do I reference an Excel sheet name in an Excel formula?

How do I reference an Excel sheet name in an Excel formula?
0
votes
2answers
17 views

If/Then (# to Text) With Multiple Number Ranges

I am using Excel and trying to create a formula. Part of the formula works, the next doesn't. I'll explain. Column P is "BPM" (Beats Per Minute) and will have a numeric value, which will be entered ...
0
votes
1answer
24 views

Easy Sum formula

What formula do I use for the total sum of a and b? 1 a 2 b 3 a 4 b ...
1
vote
1answer
28 views

How can I use if function in excel to count unique numbers which are not greater than given time duration

I'd like to ask something. I have bunch of transaction data containing date, time, ID like this: DATE //TIME //ID 7/6/2015 //9:30:22 //0266731550 7/6/2015 // 9:33:19 // ...
0
votes
2answers
23 views

excel copy values of cell if particular cell in same row is not empty

i have column A with first and last name of a person, column B with place of birth(not important in this case) and column C with employment date. Column C can be empty. I would like to copy values of ...
1
vote
0answers
24 views

Search in text for column of words and return the word that matches

I have two columns of data, the first (Column A) contains a whole load of text, the second (Column C) contains the "key" data. I want to see if the text in Column A is matched by any of the entries ...
0
votes
1answer
16 views

Excel Drop down lists

I have a drop down list of product names. Each of these products has an assigned group of employees. I would like to select the product name and then have the assigned employees names automatically ...
3
votes
1answer
30 views

Extract content from the formula in a referenced cell in Excel 2007

I have a cell A3 that says Name. Cell F16 contains =A3, which displays Name, just like I want it to. I need cell F26 to take the information from cell F16 and display the referenced location. In ...
0
votes
0answers
10 views

Excel - Pieces of data from large chart sheet 1, auto sort on sheet2

I have a large chart and need to take small pieces of data (3 cells each for 14 rows) and have them stack ranked on the second sheet. I need A21:A34 (Branch Name), M21:M34 (% of Goal) and K21:K34 to ...
2
votes
2answers
27 views

Excel: Calculate IF column based on another IF column

I am trying to use Excel to create a scorecard for different companies to use. The product I create needs to be a finished product - when a company representative fills out certain information on ...
1
vote
1answer
26 views

Count how many fall within a time range based on specific text

I am trying to produce a formula that counts how many times and individual registers a time between a certain time range. The table below is part of the data. So, for example, I need a formula to ...
-1
votes
0answers
27 views

Find first value above 0 in Column A and B then find the first value above 0 starting from the row where the previous value came from [on hold]

I have column A and B which are filled with values, mostly zeroes. All other values are above 0. If a value is above 0 in column A it will always be 0 in column B and vice versa. I now want excel to ...
0
votes
1answer
35 views

Count rows matching criteria for every row in excel

Hi here is a sample spreadsheet: +--------+-------+-------------+-------------------+ | Animal | Count | Animal Type | Animal Type Count | +--------+-------+-------------+-------------------+ | cat ...
0
votes
1answer
14 views

SharePoint Formula =IF(ISBLANK)

Is anyone able to tell me how to fix my formula. I have tried everything and I continue to get an error message. I am running out of options. I am trying to hide the SharePoint default date of ...
1
vote
1answer
11 views

Inserting formula through VBA in “A1” reference style

I insert this formula through VBA, Cells(1, 1).FormulaR1C1 = "=LEFT(A2;5)" But the formula which gets inserted ends up with apostrophes. =LEFT('A2';5) This works perfectly with the R1C1 ...
1
vote
1answer
12 views

Syntax for proper use of INDIRECT function using SUMIF in Openoffice spreadsheet to specify a functional range

Similar to others who have asked related questions, I have a spreadsheet sheet which summarizes different expense categories on a different sheet (called PC activity)in the same file. PC activity is ...
0
votes
1answer
33 views

Excel Formula with conditions

This feels like it should be simple but my head is hurting now and I'm struggling. I need to add a condition to an existing sheet which already contains formulas and references to other sheets. ...
0
votes
0answers
13 views

Copy Blank Count

i want to copy the blank color count under Color column from "5-Jul-15" sheet and "6-Jul-15" sheet to ABC sheet under "5-Jul-15" and "6-Jul-15" column (i.e. C13 and E13) respectively. It should ...
0
votes
1answer
38 views

Searching for multiple sub-strings within a single parent-string

I've been searching all day for a similar question to mine. Many questions come close, but my questions is a bit different: I have a list of last names in Column A. In Cell B1, I have someone's ...
6
votes
4answers
192 views

Average numbers if another column contains a certain substring text in Excel

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text. For example: (In column A) (In column B) Hello table . . . 1 ...
-2
votes
0answers
52 views

lookupvalue and return multiple values in Excel do not work

I need to pull through multiple unique IDs for expenses. i.e. one timesheet consists of 5 expense payments for one date, but each of these expenses for that day contain individual unique ID. I need to ...
0
votes
0answers
20 views

Is this possible to generate excel combo box based on cell value?

As you can see, Cell C2 got 1,2,3,4. I assume this is a student id. So, I would like to generate a combo box in Cell B8, using Cell C2's data, separated by ,. So, in this case, the combo box should ...
0
votes
3answers
22 views

In excel, how do I group duplicates according to whether or not they meet a certain condition?

I have a list of people with each row showing a person, and their usage of a platform in percentages. if a person uses more than 1 platform, they will appear in multiple rows, each for a different ...
0
votes
2answers
24 views

Counting number of each item assigned to customer with unlabelled columns

I have a table in Excel as follows: 1 2 3 4 5 6 7 Customer1 Z U A C D A A Customer2 B D J S F D B Customer3 S A C A A B B Customer4 X H A C D A A ...
0
votes
1answer
32 views

How can I get an average of times in Excel?

I Have a column of cells in excel of format hh:mm (The day is NOT included) like: 19:08 19:08 19:40 20:10 1:45 (Any Times before 12:00 noon are considered NEXT DAY) one (or more of these) is after ...
1
vote
3answers
56 views

Summarize tables of several sheets into one final Table Excel and include new sheets in summary

I have got several excel sheets, every single one of them containing a table for user information and one last sheet which contain the sum of all the previous tables. Since my table is very big, is ...
0
votes
1answer
47 views

Dynamic array/matrix generating within formula

The following formula: =TRIM(A1:A20) would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...
1
vote
3answers
52 views

How can I automatically organize data from a text file in a spreadsheet?

I have a text file like this: and I would like to organize into Excel like this: I could do this by hand manually for two data entries, but I would like to be able to automate this for a larger ...
2
votes
1answer
44 views

Use Excel to match data from a cell on a worksheet, then copy data from its neighboring column and paste on a different worksheet

I have a spreadsheet with multiple names and phone numbers next to them in each Sheet 1 - Sheet 5. Each Sheet has about 900 names on column A and their associated phone numbers next to them on column ...
4
votes
2answers
374 views

How to count if there's a cell with certain restrictions using Vlookup?

I'm finding if there at least one cell with the first character equal to "!" Formula I have used without success: =Vlookup("!";Left(N2:N49;1);1) Why doesn't it work? And what do I have to change ...
0
votes
1answer
26 views

True and False 2 sheets

I have to input a value in column I in sheet 1 from excel and in the column "D" from sheet 2 it needs to appear true or false. If there's any value in I than it should be true in "D" otherwise ...
0
votes
1answer
47 views

Excel Formula - Match

Can someone recommend a way to write a formula that will achieve the following. I can achieve these things individually but stringing together seems to be difficult. I have a list of orders with ...
0
votes
1answer
26 views

In excel how could I generate a row of data from another sheet?

In excel I have a sheet named "sales", and another one named "factor". In factor I have some fields for factor ID, customer name, product code, quantities and price. I want when I add this data to ...
1
vote
1answer
21 views

Excel - pivot date values with max function

I am trying to pivot date values in a pivot table in MS Excel. I am getting incorrect date values. I do not want "Grand Total" as they do not apply for dates. Is it possible to do such pivots in ...