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

learn more… | top users | synonyms (5)

0
votes
2answers
17 views

Excel: Fiscal year starts on a specific date each year

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
14 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
20 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
12 views

Excel If Statement Override and Sum

Here is what I have: =IF(SUM(J19:J29)=Quote!J55,Quote!J55, "ERROR") The sum of the rows must equal the total in a cell of another sheet or it reads ERROR. Then I have Alternative totals that can ...
0
votes
0answers
3 views

How Do You Replace a Formula With Its Result? [migrated]

I've been searching and searching but haven't found what I think is the right answer. I'm not an advanced user of excel. At best, somewhere between beginner and advanced. I created a Google ...
0
votes
1answer
21 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
0answers
12 views

Google Docs Formula [migrated]

I have a problem creating a formula for my sheets. I want google docs to put either "yes" or "No" in a specific cell depending on the values in other cell. in other words I want it to put "Yes" when ...
0
votes
1answer
32 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
49 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
30 views

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

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
0answers
17 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
29 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
45 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
16 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
10 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
20 views

excel formula required to clear many specified cells in different sheets on the same workbook - [on hold]

i.e. on sheet 1 I want to instruct many different specific cells in other sheets in same workbook to clear text - so as i can rewrite into these cleared cells at a later time therefore cannot have ...
0
votes
0answers
5 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
24 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
29 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
18 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
41 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 ...
0
votes
0answers
17 views

Get information from one worksheet and move it to another worksheet [on hold]

Basically, I could do this manually, but I have a lot of information and it would take me a lot of time. I am trying to find the right formula. I have 8 worksheets in Excel. Details Date ...
-1
votes
0answers
16 views

Why is Index and Match Formula showing incorrect value?

I am trying to pull some value from 2 combined column using this array formula. {=INDEX($E$27:$E$35,MATCH(1,(H27=$B$27:$B$35)*(I27=$C$27:$C$35),0))} The problem is the value shown is always ...
2
votes
2answers
33 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
46 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
24 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
57 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
15 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
29 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
27 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
21 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
0answers
58 views

iF cell L1 = “Paid” show cell c2 d2 advice needed

I have a formula that looks at cell L if it = "Paid" if blank cell C client name and D Solicitor does not need to be recorded on another sheet. This formula works for me so far ...
-1
votes
3answers
58 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
51 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
27 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
43 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
38 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
30 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
1answer
36 views

Excel freezes for a few seconds

We have a strange problem with Excel 2013 where all excel options will grayed out when we use CTRL+SHIFT+END button to select all the cells. During this time we will not be able to select any cell or ...
0
votes
0answers
22 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
21 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
33 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
41 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
14 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
26 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
1answer
48 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
1answer
35 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
2answers
16 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
1answer
36 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 ...