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

learn more… | top users | synonyms (5)

0
votes
0answers
125 views

Check if substring of cell in one row matches with another cell on column values in another row

I have a table with 2 columns (ID number, Description) as shown below: ID no Description 1001 A letter to george william 1001 ...
3
votes
1answer
96 views

Excel Formula to Check if a Word Begins with a Keyword

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc. I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d. I'd like to check, for each word in Column B, that if it ...
0
votes
1answer
23 views

MS Excel: Count the max spaces between a number frequency

I have a column of numbers (i.e. 7,2,5,2,6,7,5,3,7,8,9,2,3,5,6,7) What I am trying to do is count how many numbers are between each 7. Not just the first, second, third, etc. but each instance. So, ...
0
votes
1answer
30 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
0answers
15 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
2answers
39 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
1answer
34 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
2answers
61 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
0answers
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
1answer
32 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
2answers
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
1answer
30 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
3answers
55 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
1answer
27 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
3answers
63 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
1answer
61 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
1answer
50 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
3answers
60 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
1answer
42 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
1answer
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
2answers
120 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
2answers
117 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
1answer
30 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
1answer
60 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
0answers
25 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
1answer
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
2answers
38 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
1answer
25 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
2answers
52 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
2answers
45 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
0answers
55 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
0answers
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
3answers
757 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
1answer
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
0answers
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
1answer
33 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
1answer
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
1answer
34 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
3answers
73 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
0answers
96 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
1answer
31 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
1answer
53 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
0answers
64 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
2answers
34 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
0answers
32 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
1answer
31 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
1answer
75 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
0answers
162 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
0answers
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
1answer
37 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 ...