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

learn more… | top users | synonyms (5)

0
votes
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
2answers
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
0answers
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
2answers
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
0answers
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
1answer
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
1answer
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
2answers
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
2answers
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
0answers
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
1answer
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
2answers
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
2answers
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
0answers
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
1answer
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
0answers
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
2answers
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 ...