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

**0**

votes

**3**answers

43 views

### Excel Averageifs with multiple conditions in one column

I am looking for some help for how to tidy the second formula below in bold. I am looking to calculate the average 'Pupil_Score' (this is also my first try with named ranges) based on race, I am ...

**0**

votes

**1**answer

39 views

### how to create persistent random numbers &letters in excel?

Below is link for example sheets https://docs.google.com/spreadsheets/d/1D-D29csRGAAZl_h7fyMwLJ6am0FpZAtCZ5r0XlmQVqo/edit?usp=sharing. This is a live spreadsheet, which can't be pasted in this ...

**0**

votes

**1**answer

46 views

### Calculate the difference in hours from now and next workday's 9am

How do I calculate the difference in hours from now and the next workday's 9am?
If NOW() is Thursday 6pm and the next workday is Friday, the function would then return 15.
If NOW() is Friday 5pm and ...

**1**

vote

**3**answers

58 views

### Checking whether a label exists between two other labels in a column

I have a column (call it A) in Excel lists off different items and their properties. Every item has the word "Type" associated with it and so there is a cell with that label in column A so that in ...

**-1**

votes

**1**answer

37 views

### Excel- SUM formula not working on IF formula converted data [closed]

In excel I used an if formula to change values to 1 or 0. This worked fine. I tried to sum the data from J2:J2103 but it always shows a zero for the total. I used auto sum and it showed the total to ...

**0**

votes

**1**answer

20 views

### Looking up the row number of a shared identifier in Excel, and counting the number of cells in that row that meet specific criteria?

I have a sheet that contains a lookup value in columns. I want Excel to look for each value in another sheet, and count the number of instances of a certain word in the row in which it found that ...

**0**

votes

**2**answers

75 views

### how to check for a value in excel and populate column based on it

I have the following query :
My input table is
Basically I want to lookup columns for each ID.
Value3>Value2>Value1
If value3 and value column has data then ouput should show ID having value3
If ...

**0**

votes

**2**answers

70 views

### Excel Formula- Having a number automtically divide in half after 6 months

I'm tracking Transitional Housing in a Microsoft Excel- we pay a client's full rent for 6 months, and then half of that for the following 3. Is there a simple excel formula that can automatically half ...

**0**

votes

**1**answer

24 views

### Insert formula for each row in Excel or LibreOffice

I have the following data in LibreOffice:
As you can see there are 3 sets of measurement. I want to automatically feel in the D column with the following formula automatically. For example D2 will ...

**0**

votes

**1**answer

37 views

### Variable file name in cell reference as part of longer string using OneDrive

I want my spreadsheet to be easily replicated with each new year. I've already got an input section that allows me to enter the present year (for instance 2014 in A1), generate the prior year ...

**0**

votes

**0**answers

21 views

### Formula to copy rows to a new worksheet based on a single value in a row

I need to extract complete rows from a spreadsheet to a new spreadsheet based on a specified value in the row. Cannot be advanced filtering since that only allows me to copy into same worksheet. Is ...

**0**

votes

**1**answer

26 views

### Add sum for records with duplicate titles

I have a spreadsheet with quantities that I needed added up for school names that are identical. Not all school names are identical and need to be added.
My data is 3000 rows long, below is a ...

**1**

vote

**2**answers

31 views

### Appending Group Labels to Discrete Values in Excel

Is there a way in Excel to, after providing a list of groups and a column of discrete values, map a group label to each discrete value? How can I tell Excel to essentially check which group the value ...

**0**

votes

**1**answer

23 views

### Compare to column in excel and return a value from corresponding third column to a new column

for example i have 3 column:
I whant the result in a new column as:
by comparing C with A return B

**-1**

votes

**2**answers

46 views

### formula to work percentage [closed]

I am trying to find a formula that will perform a specific function.
I have two values, for example 15 and 50. 15 (b6) is the number of questions I have correct and 50 (c6) is the total number of ...

**2**

votes

**2**answers

40 views

### Excel formula to populate from one column only if another is empty?

I want column U to equal the value of column P. However, if column P says #N/A N/A then I want it to populate from column L instead. Is there a formula that will do this for me? Sorry if I've not ...

**0**

votes

**0**answers

50 views

### Microsoft Excel Starter 2010

I have multiple columns/tables of data that I want to use in what I think would be an if/then formula BUT I have more than 2 options ie. I want to be able to type in a number (62111) and have it auto ...

**0**

votes

**0**answers

32 views

### Formula entered in a cell on sheet A referencing sheet B populates in sheet B

I'm seeing an error when entering formulae which reference another worksheet. This typically only happens with large workbooks.
Example: I have two sheets, DataSheet and OutputSheet. On OutputSheet I ...

**0**

votes

**3**answers

527 views

### Excel VBA or Formula to convert text to Date with Time type

I have a quite a number of data where its date/timestamp has been imported into excel as text. Excel does not really recognize it as date with time. e.g. 25.08.2011 16:17:59 (mm.dd.yyyy hh:mm:ss)
...

**0**

votes

**1**answer

20 views

### Refer to another worksheet that has increment in cell number

I have a worksheet (let me call it WorksheetA) that each row has a different information. Then, I want to refer this information per row on my other worksheet (WorksheetB).
To explain further, ...

**0**

votes

**0**answers

19 views

### Excel - How to get list text to display [duplicate]

How do I write this excel cell formula?
So I have a RowA which has a list of words and they each correspond to RowB with an "!" or an "X".
Row A : This is a list of words
Row B ...

**0**

votes

**1**answer

31 views

### Select text to display based on matching a symbol in another row

How do I write this excel cell formula?
In Row 1 is a list of words and they each correspond to an "!" or an "X" in Row 2. Only one word is given an "!".
[A] [B] [C] [D] [E] ...

**0**

votes

**1**answer

29 views

### how to display names of columns that meet a certain criteria

I have an excel 2007 document with 4 columns (headings english, maths, physics, result) and 5 rows.
The rows and columns (cells) are filled with letter grades a, b+, b, c, e, d and f.
required:
...

**0**

votes

**1**answer

22 views

### Is it possible to combine HLOOKUP with AVERAGE to get the average of a column if the title matches?

I have a very wide data set from which I need to average certain columns on a different sheet. So I want to list the column headers that I want to average, and then put a formula in that will HLOOKUP ...

**-1**

votes

**3**answers

69 views

### excel if statement 0 value

I am creating an if statement which helps calculate the difference between two cells and displays the difference as a percentage- here's an example of the code I am using - =IF(D19=E19,"Values are ...

**0**

votes

**0**answers

83 views

### Auto propagation of formulas down the columns for each new row

How to propagate those formulas down the respective colums for every new row automatically? There is an webhook which creates new rows with data with those columns (A1, X2, Y2) empty, and I would need ...

**-1**

votes

**1**answer

30 views

### The value given by the Excel NOW() function

I have an Excel sheet that does a some SUMIFS() over manually entered data, including some kind of test for entry date.
When I enter a new data line, I first type =NOW() in the cell then copy-paste ...

**0**

votes

**1**answer

49 views

### Auto sequential numbering based on cell criteria with two numbering sequences and ability to ignore duplicates

In the example below the numbering in column A skips rows that have the word "TOTAL" in column C but picks right up again in the next rows. The rows with the word "TOTAL" in column C have their own ...

**0**

votes

**0**answers

51 views

### Notepad++ vlookup to Excel

I have an XML file that has some specific incorrect text within it. The incorrect text is associated to a specific code that identifies it,. Eg: CODE="RM014" PART="ROOM MONITOR".
The issue is that ...

**0**

votes

**2**answers

32 views

### Excel Formula to deduct the amount of a text

I want a formula in excel.10 that I have some amounts in Column "A" and their comments in column "B". If I write the text "RECEIVED" in Cell B5 so it automatically deduct the amount of cell "A5" from ...

**0**

votes

**0**answers

31 views

### Xcel spreadsheet formula for sorting qualifying names

I am trying to set up a spread sheet to determine qualification for employee of the month. I am able to insert all the employees that qualify under a certain criteria. How can I make a formula that ...

**0**

votes

**1**answer

27 views

### lookup col a or b and find match - return column 3

I am trying to compare two different sheets
Sheet one has company names
sheet two has company names in two different columns
note the company names are not exactly the same, i.e one may have a comma ...

**0**

votes

**1**answer

62 views

### Excel Formula to count unique cells in a range with Condition in another column [duplicate]

I am using the following formula to count the unique values in a range:
=SUMPRODUCT(('Sheet 1'!$E$5:$E$900<>"")/COUNTIF('Sheet 1'!$E$5:$E$900,'Sheet 1'!$E$5:$E$900&""))
This formula is on ...

**0**

votes

**0**answers

109 views

### Trying to get VLOOKUP to get data from a table array on another workbook where the worksheet name is variable

I currently pull data in from 4 workbooks using the INDIRECT function, the formula is as follows,
=INDIRECT("'[" & $AE$1 & "]" & $AA$13 &"'!" & $AI$1)
$AE$1 contains the workbook ...

**0**

votes

**0**answers

17 views

### Alternate row colors only if there are contents in them

I want to alternate row colors in an OpenOffice spreadsheet, but only if the first cell in the row has contents in it.
The following function works great to return the correct value:
...

**-1**

votes

**1**answer

32 views

### Find the highest number from multiple columns and return a name

I'm a complete novice at spreadsheets. I have created this spreadsheet by reading a lot of forums and muddling together formulas. I have now hit a problem that I can neither fix nor find out how to ...

**0**

votes

**1**answer

229 views

### Excel formula to count when value changes?

I have an Excel conundrum I can't seem to crack.
In Column A I have a long list of counties, and each of those counties is a member of a larger group of counties that pertain to a particular funding ...

**1**

vote

**1**answer

42 views

### Make Excel formula yield `blank` as an ISBLANK() = TRUE

Make Excel formula yield blank as an ISBLANK() = TRUE
I found couple poster asking about what are the differences between "" and simply (blank), but now how do make formula return blank?
Example:
...

**0**

votes

**2**answers

20 views

### Does a range of cells contain a value in a list?

I want to answer - does a range of cells (A1:E1) contain any value from a list (G1:G50)
In other words: Does A1 OR B1 OR C1 OR D1 OR E1 contain any value that appears in G1:G50?

**0**

votes

**1**answer

111 views

### Excel, how to check cell value against a column of values?

Using excel 2007, I have 2 worksheets.
The first one is 1 column of about 600 numbers/IDs.
The 2 worksheet is about 30 columns for about 5000 IDs.
Im basically trying to filter out the 600 IDs ...

**1**

vote

**2**answers

269 views

### Convert Text Value to Time Value in excel

I am trying to convert text to time. Basically I copy and pasted the total time of employees but it pastes as text value and then in order to add the time I have to convert it to time and I have to ...

**0**

votes

**1**answer

53 views

### Index/Sumproduct calculation error

I have been working on an excel problem for the better part of 24 hours. I have a formula that almost does what I want but it is not returning what I imagine it should return:
...

**1**

vote

**1**answer

56 views

### How to count blank cells at end of row in n-column table

Suppose I have a table with 50 rows and 50 columns. Each row has a certain number of blank cells, often occurring in the last few columns, with the first few columns being relatively dense. I would ...

**0**

votes

**1**answer

51 views

### How to combine two columns with no data loss for further analysis

Hopefully you good people can help me as I'm pulling my hair out doing this manually and can see no way to automate
At present I have two sets of data consisting of three columns each - Date, Amount ...

**0**

votes

**1**answer

28 views

### Is there a function to get the cell your equation is pointing at as the output rather than the value inside the cell?

For example if I had an function such as vlookup that put me into a cell could I then have another function that gives what cell it found rather then the value inside the cell?

**-1**

votes

**2**answers

45 views

### How to compare multiple columns and set values based off of it

I Reference to the picture in the link below, I need to find the best way to compare which Start Month and End Month row the current month is between and then set a cell somewhere else in the spread ...

**-1**

votes

**1**answer

20 views

### How do I reference a specific row number using a defined column name?

I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default details to use if the specific ...

**-1**

votes

**1**answer

62 views

### How to make COUNTIF only count subtotal for visible records when filter is applied

I would like a formula to COUNTIF the Brand 3 column and if filtered the COUNTIF changes to filtered criteria.
TOTAL COUNT 0 0 3 COUNTIF(H3:H5,"VoID")
Area ...

**0**

votes

**0**answers

25 views

### Copy distinct values in calc

I have some text data in column A, adding data to that column is the standard use case. I would like Calc to dynamically copy only unique values to, say, column D.
I am aware of OpenOffice: get ...

**0**

votes

**2**answers

113 views

### Find partial text in Excel 2013

In Excel 2013 I would like to find the value in column B in the row where a string matches a string in column A. What complicates it is that the strings in column A are allowed to be shorter than the ...