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

**0**

votes

**1**answer

31 views

### Excel logic AND and OR functions

Does my formula below need to read (Occupancy=1)*(Gender=2) to consider the occupancy for both genders?
=(MIN(IF((Occupancy=1)*(Gender=1)+(Gender=2),Gross_Rev)))
I guess as a general question, ...

**0**

votes

**0**answers

16 views

### Excel 2010 “lastcell” issue with formula in complete column

I'm currently using a formula throughout a column in an Excel 2010 worksheet. It works fine, but I'm not really using the entire worksheet, I only don't want to have to worry about adding the formula ...

**0**

votes

**2**answers

41 views

### Make Excel marks the repeated rows with a lot of data in each row

I have this Excel archive with 5 columns of data, and then, about 4,000 rows of different data for each column, but I can’t find a way to make that Excel marks the repeated rows?
I have tried using ...

**0**

votes

**1**answer

41 views

### Sum values in range if lower than a threshold otherwise sum the threshold value

As per the title, the best I could come up with is this, however it yields an error. My unremarkable knowledge of Excel prevents me from making further progress.
This example formula should add all ...

**0**

votes

**2**answers

70 views

### Excel: how to remove leading characters of a string with a function?

I have a text column in Excel full of hexademical values like this:
0000000000000c10
00000000000036f0
00000000000274da
00000000000379e0
Which function can help me strip the leading 0's up until the ...

**0**

votes

**0**answers

19 views

### Spreadsheet Function Help - From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

I have posted this question before, and got half-solution. Pls have a look at this link to get an idea.
http://yogi--anand-consulting.blogspot.in/2014/11/yogifrom-table-of-names-and-birth-dates.html
...

**0**

votes

**1**answer

37 views

### Excel formula - Return value from lower row

I'm looking for a way to search for a value in a single column and return a value in that same column two below it. Example:
A1: Apples
A2: Bananas
A3: Oranges
A4: Pears
If I search for ...

**0**

votes

**2**answers

25 views

### Why is using this code to replace a formula giving me an error?

In Excel I use the following to clear cell E5 and replace it with the word name.
Sheets("Invoice").Range("E5") = "Name"
I can also use the following to replace G11 with the VLOOKUP code I have here ...

**0**

votes

**1**answer

31 views

### Excel Conditional sum on same row

I have a rather basic Excel question, but my Excel skills suck.
What I want is a conditional sum, but I'm not sure how to do it.
Example:
A B C D E
...

**0**

votes

**3**answers

61 views

### How do I get the fiscal year to start on a specific date each year in Excel?

I used the formula listed here to generate a fiscal year based on the current month. I have an odd requirement where she wants the fiscal year to start on 6/20. For example, any dates ranging from ...

**0**

votes

**1**answer

29 views

### Subtract between two sets if one has value in the same row

In one row I have two possible places for times to be completed, Day Shift and Night Shift.
I want to do a simple subtraction where the cells are completed (Example =O9-S9 or =K9-G9 where there are ...

**0**

votes

**3**answers

83 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

77 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

61 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

64 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

44 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

24 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

186 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

183 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

35 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

75 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

33 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

40 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

27 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

59 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

52 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

57 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

34 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

1k 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

23 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

34 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

30 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

48 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

75 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

108 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

34 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

57 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

78 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

42 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

33 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

34 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

95 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

**2**answers

83 views

### How to compare two columns with other two columns for duplicates in Excel

I need to compare columns C and D with A and B and only if exist same combination of two cells in one row. Hope the image below will explain the situation better.

**0**

votes

**0**answers

254 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

19 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

42 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

621 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

58 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:
...