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

learn more… | top users | synonyms (5)

0
votes
1answer
37 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
31 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 ...
4
votes
1answer
373 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
33 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
15 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
41 views

Can I make Excel keep a history count when a selection is made? [on hold]

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
13 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
21 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
19 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
21 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
62 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
21 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
48 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
15 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
7 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
39 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
1answer
28 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
24 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
36 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
38 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
28 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
34 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
43 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
28 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
28 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 ...
-2
votes
0answers
41 views

Google sheets/excel array formula return row of values based on =TODAY()

I am collecting data from a Google form to a spreadsheet and then capturing that information into a 2nd spreadsheet for manipulation. From here I want to display the same information on a 3rd sheet ...
1
vote
1answer
39 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
38 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
49 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
22 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 ...
-1
votes
0answers
17 views

Excel Auto Fill Date

I am using Excel to keep track of my finances. I have created a column for the date (Column A) using the today() excel function. How can I automate it for multiple entries for the same day? So for ...
-2
votes
1answer
37 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
73 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
48 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
23 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
84 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
16 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
37 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 ...
1
vote
1answer
71 views

Change excel formula references when dragging across columns

I have a row that I want to populate with formulas like =SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49) =SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48) =SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47) but dragging ...
1
vote
0answers
52 views

EXCEL 2011 - Inventory with Matrix and Vlookup

I am creating an inventory matrix, which has on the first column a list of all the items in the inventory, and on the first row a list of the dates for when the inventory gets renewed. Basically, not ...
0
votes
0answers
15 views

Daily spend vs projection spend pacing (%) [migrated]

I have a google doc sheet which currently has all our accounts spends.There are two tabs.The first tab details our actual daily spend for each account for each day of the month.The other tab is a ...
0
votes
1answer
44 views

Assigning values to rows based on keywords [duplicate]

My bank statement has a description column which tells me where I spent the money or where I got it from. Based on this information I fill a column manually to be able to catagroize these expenses - ...
1
vote
1answer
24 views

How to determine largest individual value in excel in a group of identical cells?

I am trying to figure out Column D Which SKU has highest total? how do I do that in excel with a formula? In this case I determined it manually, I tried IF=IF(A2=A3,IF(C2>C3,"Largest","NOT Largest")) ...
0
votes
1answer
31 views

Adding value of rows if column A and B cells are similar in Excel

I have a situation. I have three columns | A | B | C | |Category | Sub | Price | Column A is basically Category column, column B is Sub Category and "C" is price. I need to add the ...
2
votes
2answers
88 views

IF statement formula for overdue DVD rentals

I am creating a spreadsheet for my local parish church to keep track of borrowed DVDs: The DVDs have to be returned 14 days after they are borrowed. If the current date is over 14 days after the ...
0
votes
2answers
82 views

If Difference Higher or Lower than Display

I need a excel formula that will show: * "1" if B is less than A by more than 2 * "0" if B is within 2 of A * "-1" if B is higher than A by more than 2 Can someone please tell me whether this is ...
0
votes
1answer
40 views

Calculate time difference in percentage

I'm trying to make some statistical data on the performance of a specific task. One of the data points gathered is how long time it took to run. I'd then like to compare that against the expected ...
0
votes
1answer
26 views

Excel formula to show multiple cell address or row locations?

I'm using the following formula, which returns a numeric value: =SUMPRODUCT((A3:A176="Thing1")*(B3:B176="Thing2")*(C3:C176="Thing3")*(U3:U176="No")) However, I'd like to also know the cell address ...
0
votes
1answer
41 views

Extract text between emails with < >

Original link Extracting Data Between two Characters in Excel I have a combination of cells where some have nicknames and some without nicknames. Some will have nickname < > emails and same will ...
0
votes
1answer
127 views

How to return all the cells in a row if first column matches a value on another worksheet

I have 2 worksheet tabs in an Excel workbook, tickets and tickets info. I have values in Column A of tickets (from A2:A500). I want to search for each value from A2 to A500 of the ticket sheet looking ...