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

**0**

votes

**2**answers

336 views

### Fill cell with sheet name Microsoft Excel

I have an excel file which has around 100 sheets in it. Each sheet is for a student, and each sheet name is the firstname_lastname e.g Mark_Hope so what I want to do is that in each sheet there are ...

**0**

votes

**1**answer

102 views

### Group values in Excel

I have 500 rows of items. I need to enter these in an application where I can enter a maximum of six items at once by creating a unique Group ID for these six items.
The items in a group share the ...

**0**

votes

**2**answers

31 views

### How do instruct a cell to display a minimum value?

I currently have the following formula in a cell:-
=CEILING(E9*0.85, 1.25)
I would like the cell containing this formula to display a value no less than 20 but only when the value of E9 causes ...

**-1**

votes

**1**answer

742 views

### Calculating Stock Profit/Loss in Excel

I'm trying to create simple spreadsheet to calculate profit/loss after every complete transaction I perform on the stock market. This way I'll be able to track my portfolio performance at any moment ...

**1**

vote

**1**answer

50 views

### Compare efficiency of excel formulas

Is there any way to directly compare for calculation efficiency side by side two different formulas for achieving the same goal in excel?
Say I want to know which is more processor intensive (or if ...

**0**

votes

**1**answer

149 views

### Insert pop out table into excel cell?

Is it possible to create a pop out table/dialog in a clickable cell in Excel 2010? I would then like the sum of the table to show up in the cell.
Right now I am using a simple workaround, wherein I ...

**0**

votes

**1**answer

167 views

### How do I set a dropdown in excel to populate different data in cells below based upon the option selected from the dropdown?

I am attempting to clean up a spreadsheet that we have that tells us what permissions to grant for certain roles in our ticketing system.
I have used the Data Validation tool to create my dropdown ...

**1**

vote

**1**answer

101 views

### Date range - month inclusion formula

I need a formula to look at a date range, i.e., start and end date within 1 calendar year and return yes or no if a named month (in a fixed cell) falls within that range:
ColA ColB ...

**0**

votes

**1**answer

301 views

### How can I pull specific rows in Excel into a new spreadsheet

I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample.
Is there a formula I ...

**0**

votes

**0**answers

28 views

### Spreadsheet: Normalising multiple irregularly time-stamped data from sensors only roughly in sinc

I use the latest version of open office on a windows vista pc. I have .csv barometric data logged from two different sensors (pieces of equipment), that report when changes occur rather than at set ...

**0**

votes

**2**answers

2k views

### Determine week in fiscal year in Excel

I am trying to get the 1st-53rd week of the fiscal year for the dataset ranging from 2/1/14-1/31/15. I am putting the first day of the fiscal year as a separate week.
The =WEEKNUM() functions gives ...

**-1**

votes

**1**answer

48 views

### Show value from every sixth column with a formula that can be filled down a column

I have two Excel sheets. On Sheet A, I have several columns of data, and the average of each column is in row 41. On Sheet B, I would like to have a column that shows the averages from Sheet A. The ...

**0**

votes

**0**answers

53 views

### sum rows and columns depending on two values

I got the following table in Excel (Microsoft Excel 2011 for Mac).
It is basically for controlling issues. It shows a number of persons (employees) that are assigned to specific work-packages that ...

**0**

votes

**0**answers

15 views

### What formula in OpenOffice calc can automate game show points as needed? Details in text

I host a trivia game show in which the answer to every question is a number. The eleven teams/players closest to the correct answer without going over receive points - 15, 12, 10, 8, 7, 6, 5, 4, 3, 2, ...

**0**

votes

**1**answer

310 views

### Excel - How to check if and in how many rows a certain value/string is present in range

I have a list A of strings and I want to see if and if yes in how many rows each string is represented in range B. My data looks something like this:
List A:
aaa
bbb
ccc
Range B:
1 aaa --- ---
...

**0**

votes

**0**answers

97 views

### Lookup and return values from unsorted sheet

I have a workbook set up something like this:
Sheet 1 has data for some people: name, address, height, favorite color, and many other things.
In sheet 2, I want to pull all of the data for each ...

**5**

votes

**1**answer

1k views

### Does Excel always calculate hidden rows?

If I have Excel sheets with long filtered lists, and I recalculate while a filter is applied, when I remove or change filters, can I always guarantee that Excel will have already calculated all hidden ...

**0**

votes

**0**answers

79 views

### Excel: Count duplicates only once, based on third criterion

So I got a excel sheet that lists (amongst other things) organizations and the year they were active in. Some organizations were active more than once a year. It looks like this:
YEAR -- ...

**1**

vote

**1**answer

22 views

### Conditional Formatting in Excel via a different worksheet

I've been trying to figure out how to make a cell bold and color red based on conditions entirely in a different worksheet. For example, I'd like Sheet1!A1 to be bold and red when Sheet2!A1 = ...

**1**

vote

**0**answers

70 views

### Can I make Excel keep a history count when a selection is made? [closed]

I have a drop down list people (Data on Sheet#2) and I would like to keep a running total every time these people are selected under the drop down list for the type they were selected for.
Example: ...

**1**

vote

**1**answer

39 views

### In LibreOffice Calc is there a hotkey to edit the current cell?

I am wanting to edit a column of cells (for example, copy/pasted values have $0.005/hr and I want them to just be 0.005)
I can navigate with arrow keys, I can click to edit the existing value, but ...

**0**

votes

**1**answer

53 views

### How can I use vlookup function for multiple table array that has different length?

I need to import the minimum value form Column C to column B for each item, but the problem is that the table array is different for every item.
Since I can't upload any image, I will just type the ...

**0**

votes

**1**answer

50 views

### Adding Text to Existing Data in Excel

Is there a way in Excel to add text without spaces to the ending text of contents of a column. For example: I have 600 records and I want to add the letters OP directly after the contents of all ...

**1**

vote

**2**answers

61 views

### Comma inside inverted commas in an IF statement

In the formula, IF[1000,",",1/3*27]
Please can you let me know what the "," represents?

**1**

vote

**1**answer

1k views

### Excel: Count duplicates only once, based on other criteria

I need a formula that counts only unique values based on a third criteria.
So I got a excel sheet that lists different organizations, the location they were active in, and the year they were active ...

**0**

votes

**0**answers

33 views

### Formulas suddenly become text

I've been working on an extensive Excel list with cell references between sheets and it's been working like a charm, up until now. There is this one cell that refuses to acknowledge that I have ...

**1**

vote

**1**answer

4k views

### Switch Calc (or Excel) formulas to English language

I'm not using an American/English version of Calc/Excel so, every time I want to post here (or on other "web-places") or when I want to try formulas posted by others I need to "translate" them somehow ...

**0**

votes

**1**answer

19 views

### Summing the above 7 rows in same column based on a value

I have a series of values that repeat like days of the week doing down in the same column.
Monday : 2
Tuesday : 5
Wednesday: 6
Thursday : 1
Friday : 8
Saturday : 12
Sunday : 4
Monday : 3
...

**0**

votes

**0**answers

9 views

### Auto update filter - no macros

Is there a way to automatically update a filter without hitting "reapply", without using macros?
I have a dropdown list and would like the worksheet to filter based on the coice from the drop down.

**1**

vote

**1**answer

113 views

### Combine text contents of two cells ONLY if one has an entry

I want to copy the contents of two cells as an easy "cheat" to be able to copy the text for entry somewhere else.
The first cell (D2) is a name. Example: Smith, John
The second cell (AG2) is a note. ...

**0**

votes

**2**answers

336 views

### How do I highlight the max values across every column in a pivot table?

Looking for a way to use conditional formatting to highlight the max value in every column of a pivot table.

**0**

votes

**2**answers

28 views

### Excel not to start calculation on Automatic mode if data is put wrong

I want excel to stop calculation if invalid date is put. I tried to use "data Validation" using below formula =AND(ISNUMBER(E3),LEFT(CELL("format",E3),1)="D").
It is working but the calculation don't ...

**0**

votes

**1**answer

140 views

### How to identify duplicate combinations of two values in random order

I am trying to identify the second duplicate combinations of cells across two (or more) columns. Complicating matters, the order of combinations varies.
My data looks something like this:
aaa 111
...

**2**

votes

**1**answer

297 views

### Creating a line graph in excel with changing/result cells

For a project we are creating care packages for the homeless. I am trying to figure how many kits to make for the least leftover material as the things we are putting into the packages are sold in ...

**0**

votes

**2**answers

118 views

### Excel Countifs date column not empty

I am trying to count number of external employees.
There are two columns,
Column F with values EXT for external and INT for internal employees
Column G with date.
Now, if date column is empty ...

**0**

votes

**0**answers

67 views

### Excel formula to find a particular sheet of a workbook from another workbook

I am looking up one workbook (Figuresmaster) to pull financial figures into another workbook (AreaBudgets). I have the below lookup formula working (in AreaBudgets).
=IF(ISNA(
...

**0**

votes

**2**answers

63 views

### Is it possible to have an Excel formula dependent on value input?

Is it possible to have Cell G24 recognize the number in D24, and do its math depending on the size of the number, using the in Cells B37 through B42?
I have a basic spreadsheet for cataloging sales ...

**0**

votes

**0**answers

92 views

### Macro, Formula or Code? Copy data to new sheet based on specific criteria?

I have tried pulling this together with various formulas but A. A lot of the didnt
work and B. it was very messy.
For the example of this question lets use a Music CD with 2 Disc.
The information is ...

**0**

votes

**1**answer

51 views

### Sum up values from two lines but keeping some columns seperate

I want to sum up the values in Column C for rows with same Column B value.
In my example the values for A Dunn and G Birch should be combined, with the bottom line going blank
But column A (account ...

**1**

vote

**1**answer

197 views

### How to automatically sort random numbers

I've got 2 columns in a spreadsheet.
Col. A contains a formula that generates a random number:
=RANDBETWEEN(1,100)
Col. B calculates the rank of the random number in A using
...

**1**

vote

**2**answers

181 views

### How to convert cells of a column to text in Excel 2007

I have a column consisting of cells (from 1 hundred to over 100 thousands) with values like :
5
12
78.456
1235.78510
AFT780.963
ZFLQ
57180.004
...
I want a way to rapidly change the format of the ...

**2**

votes

**2**answers

471 views

### Raise matrix to power without VBA

I need to raise a matrix to a large power (say 200), and I can't use VBA because it is for a final user whose IT doesn't allow macros to run.
Also since the power is large, it won't suffice to write ...

**0**

votes

**0**answers

37 views

### Excel 2007. 2nd Formula wont accept result of 1st formula on a different work sheet

I have a rather frustrating problem that it seems others have had also but I have yet to find a solution that works.
I have simplified things below as I tend to give too much information.
Basicly I ...

**-2**

votes

**1**answer

256 views

### MS365, Excel 13 - New row added not auto updating formulas

I have a table in Excel I made myself not an inserted one - though I have tried that too it didn't do anything. The problem is at the end I add a new row and none of my formulas are in the new row. My ...

**4**

votes

**2**answers

401 views

### How can I parse a string from a cell multiple time in excel?

I have a column in my datasheet that often contains a string that I want to place in another column. This string may occur multiple times and I want to place ALL instances of it into the other column. ...

**1**

vote

**2**answers

81 views

### Formula substitution excel

is it possible to do formula substitution easily in excel?
I have a worksheet similar to below example (albeit more complex, hence the need for some function in excel if there exists one)
What I ...

**0**

votes

**0**answers

59 views

### Excel linked data formula turns to #REF upon opening workbook Office 365 ProPlus

Open File A with vlookup formula referencing File B. File A will show fine. Open File B and File A will change to #REF!
I correct the #REF! and make sure to update formulas (calculate) to file B. ...

**-1**

votes

**1**answer

105 views

### Correct way to write or even better combine these two Excel 2007 formulas

I have two formulas. As an example for this question let's assume I have a workbook that is basically a CD Database. It contains all the usual information that you would expect.
This workbook is ...

**1**

vote

**0**answers

25 views

### Track workflow precendents using only Excel Formulas

what I'm trying to achieve is the following. I have a production chain, say for example:
A and B are used to make C
C and D are used to make E
and E is used to make F
And I wanna know the direct ...

**1**

vote

**2**answers

390 views

### How can I copy a column from one sheet to another sheet based on title in Excel?

I have a datasheet that has many columns that may change positions. To make it easier to use I want to create a second sheet that keeps the columns in a specified order. I can count on the column ...