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

**0**

votes

**0**answers

8 views

### SUMPRODUCT where the product is computed from the row

I have a range AG6:AS38. It looks like this:
AG AH AI AJ AK AL AM AN AO AP AQ AR AS
--------------------------------------------------------
6 | 1 A B C D ...
7 ...

**2**

votes

**1**answer

8 views

### Excel Autofill Linegraph with Time

I must admit that I am not the expert on excel, but I need help creating a graph with some data. In column A, I have over 500 cells filled with temperature values from a probe. Now, technically each ...

**-1**

votes

**0**answers

6 views

### How to consolidate information from different sources in Google Sheets

So I have a Google Sheet with several sheets (tabs) in it, like so:
Sheet 1 - Weapons Legend
A B C D A = Date
5/3 John Sword 10 B = From
6/3 ...

**0**

votes

**2**answers

28 views

### Excel formula to identify and group figures

I have two columns A and B
In column A, I have figures in some currency say US Dollars. In column B, I have account codes to which the figures should be grouped.
A B
USd Codes
2,500 ...

**0**

votes

**2**answers

88 views

### How to compare two columns with other two columns for duplicates in Excel

I need to compare columns C and D with A and B and only if exist same combination of two cells in one row. Hope the image below will explain the situation better.

**1**

vote

**2**answers

1k views

### Creating a column of working days in LibreOffice Calc

In LibreOffice Calc, I would like to create a column of working days, for example:
[...]
2012-01-30 [Monday]
2012-01-27
2012-01-26
2012-01-25
2012-01-24
2012-01-23 [Monday]
2012-01-20
2012-01-19
...

**0**

votes

**0**answers

17 views

### How can I identify filtered columns? [duplicate]

My file has many columns.
its difficult to know whether there are filters or not and I have to scroll to the right to verify each and every column.
Is there a formula to put in a cell indicating ...

**0**

votes

**1**answer

2k views

### In excel how to do a 'sumif' function that is dynamic to a changing column

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is ...

**0**

votes

**0**answers

11 views

### Formula in Google Sheets to lookup contents of another cell [migrated]

I'm trying to create a formula that brings back a certain cell depending on what another cell has in it. But the formula I'm trying isn't working. It's working for other things (what I thought was the ...

**0**

votes

**2**answers

37 views

### How do you get excel to ignore zero or blank values in an Average?

I am making a spreadsheet to score recipe ingredients, averaging scores for quality of the ingredient, packaging, and accuracy of serving size, and then using that data to create an average for the ...

**0**

votes

**2**answers

3k views

### Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are ...

**24**

votes

**7**answers

192k views

### In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.
I've named the range A3:A10 'some_names', and I'd like ...

**0**

votes

**0**answers

19 views

### SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing?
Current formula w/o rounding:
...

**0**

votes

**2**answers

20 views

### How can I prevent changes to formula results after first use?

We have an Excel 2013 workbook that we use when we create new user accounts (column A for first name, column B for last name, column C for the password, etc.).
First name Last name ...

**0**

votes

**1**answer

20 views

### Excel Month() function does not detect some date format

I have a simple table like this in excel:
The formula i used was =IFERROR(IF(LEN(B3)>0,MONTH(B3),""),""). I check if a cell is blank by using LEN, if so ignore, else, convert it to month no.
I ...

**5**

votes

**5**answers

22k views

### Is there a function in Excel to find the maximum absolute value of a range?

I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= ...

**1**

vote

**1**answer

22 views

### How to use a variable once, across the equation, in Excel 2007?

okay, is there a way to declare a variable inside a cell, without calling the cell name 3 times, across the equation(?)?
I came up with this, to calculate the age from a DOB entered using the ...

**25**

votes

**5**answers

73k views

### How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...

**0**

votes

**1**answer

12 views

### How to create a dynamic date title in excel 2010 using different tables and data

My friend has asked for help with an excel assignment and they have 2 tables, HRM and Data_Tables.
Data_Tables has corresponding month numbers from 1-12 in column I from I3-I14. In J column there is ...

**-1**

votes

**0**answers

15 views

### Add a cells content to another cell if cells at the top of the columns have only specific content

I already have a big part of this done thanks to another user, however I need it to go one step further please.
I want to add the contents of cells in a specific row, based on a SUM formula. I want ...

**1**

vote

**3**answers

23k views

### Excel Formula To Get First Non-Zero Value in Row and Return Column Header

I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right.
Below is a ...

**0**

votes

**1**answer

6k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent change from -4 to +4. A change ...

**2**

votes

**0**answers

19 views

### Taking an indirect reference in excel and making it a direct reference

For many somewhat complicated reasons -- it has to do with transposing columns and rows because of third party software limitations etc. -- I have had to use the indirect function in order to make ...

**3**

votes

**3**answers

549 views

### Convert a formula in Excel into an Array Formula without Ctrl-Shift-Enter?

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
Normal Formula
Array Formula

**0**

votes

**1**answer

27 views

### Simple sum of dropbox values for scoring Excel 2013 worksheet

I would like to make a worksheet with dropboxes for scoring ingredients and summing the scores per ingredient. I already made the dropboxes with number 1-3(1=poor, 2=fair, and 3=good), but I would ...

**0**

votes

**2**answers

36 views

### Check if a cell contains any of words from a dynamic list

I want to use a formula to populate the "RESULTS" cells. I need it to check if the adjacent "CHECKED CELL" contains any of the words listed in the "KEYWORDS" cells.
Ideally, I would be able to add ...

**-1**

votes

**0**answers

16 views

### How can a cell be displayed if another cell is marked?

I am a trainer for an organization, and we have a major conversion to a new core system coming up. We have been provided a massive spreadsheet listing all the training modules that each person must ...

**2**

votes

**0**answers

18 views

### How to output a CSV file from a matrix of Excel formulas using multiple files?

I am receiving on a weekly basis a list of Excel spreadsheets from a supplier (around 40 spreadsheets) with data in a specific format. Since the format is a bit complex and can't be converted to CSV ...

**1**

vote

**1**answer

14 views

### How to create a text mask in Excel 2007

I have a list of numbers:
59046
64940
77505
101409
140331
1036135
3166909
I want every number to be exectly 9 digits.
For example, i want 59046 as 000059046, 1036135 as 001036135.
How can i create a ...

**23**

votes

**4**answers

65k views

### Is it possible to sum an entire column without setting explicit cell boundaries in Excel?

I'd like to sum all the values in a column starting from a certain minimum value, but I don't want to give an upper bound because any value in the table will be appropriate for summing (minus the ...

**-2**

votes

**1**answer

22 views

### Add a cell content to another cell if a different cell has a specific content

How can I fill a cells value (e.g row 29 col A) with =sum(B1:Z1) but then only add the content of a cell in that row 29 if row 4 of that column says "UK IR". That way I can add all the cells together ...

**0**

votes

**1**answer

27 views

### Attendance Tracker - Deduct points if no points within pay period

I have a spreadsheet where I keep track of whether or not someone called in, was late or left early. On the 1st sheet I have their names, the number of times they've called in, were late or left ...

**10**

votes

**4**answers

29k views

### Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...

**0**

votes

**2**answers

2k views

### Calculate different discount levels in Excel

I have to create a price list with 3 different discount levels called eg. E1, E2, E3.
E1 = 10%, E2 = 20% and E3 =30% discount.
How do I write a formula to tell Excel if the item falls in class E1 it ...

**0**

votes

**0**answers

18 views

### Most efficient function(s) to compare and extract rows from two reports? [on hold]

I work for an international shipping company processing hundreds of containers each day. I generate a report (from an AS 11g servlet, FWIW) in the morning, work through it based on the 2 Modes of ...

**0**

votes

**1**answer

7 views

### Hyperlinking or linking in another way to a cell without it being able to change

How do I hyperlink or link to a cell from another cell in a way that when rows are added, and the row numbers keeps changing due to new rows of information being added throughout the sheet, the link ...

**1**

vote

**1**answer

18 views

### Sumifs function where I want to sum a range not just a single row or column

I am stuck.
I am entering timesheet date into an excel sheet:
Column A - Resource Name
Column B - Project Phase
Column C - Resource Team...
Column D to AAA is a date
Therefore a single resource could ...

**0**

votes

**0**answers

120 views

### Reference last cell not only in a row or column, but in a range

all users, especially Jerry Beaucaire.
I was reading this link when I stumbled upon his answer
Here is the link
How to refer to the last cell in a row in Excel?
And Jerry Beaucaire answered it like ...

**0**

votes

**0**answers

22 views

### How can I copy new data with additional rows, while including formulas?

As an example I have data in the range A1:D55, and in Column E I have a formula using VLOOKUP in reference to column A.
I pull the data again for A1:D55 and the values change (titles remain the same) ...

**-1**

votes

**0**answers

19 views

### Excel Formula will not update [closed]

I have a formula in excel that will not update automatically even though it is set to do so. If I hit F9 it still will not update. I have to be in the formula bar and hit enter for the update. Any ...

**2**

votes

**3**answers

6k views

### Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...

**23**

votes

**8**answers

320k views

### How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them.
Suppose
Col A has 50 numbers i.e 0511234567
Col B has 100 numbers in the same format

**1**

vote

**1**answer

42 views

### How can I replace forbidden characters in a file names before saving in VBA?

This will be very simple for somebody who knows VBA better than me. I used a code original provided by user Nixda on this forum to split excel worksheet into multiple CSV files based on column value ...

**0**

votes

**2**answers

37 views

### Excel: Validation rule with value that should be in the list of another column without blank

On excel, I've two tabs: one listing all people participating to an event, with a lot of details. I've a "FullName" column which is basically defined by:
=[@LastName]&" "&[@FirstName]
This ...

**0**

votes

**1**answer

32 views

### Multiple Formulas in one Cell for hotel

I am trying to create a housekeeping spreadsheet where when a room number is entered into a cell a 0 or 1 (0 is for studio and 1 is for a 1 bedroom) is automatically populated in another cell. I have ...

**4**

votes

**2**answers

4k views

### How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula:
=GoogleFinance("GOOG", "price")
to download the latest price of a stock. Is there an equivalent function in Excel 2013?
Earlier versions ...

**1**

vote

**1**answer

36 views

### How to count months in intersection of two date ranges [closed]

I need to calculate the number of months in the intersection of two date ranges.
For instance, in Column A I have start dates of employment
12/1/1998, 1/1/2003, 2/1/1995
and in column B I have ...

**0**

votes

**1**answer

21 views

### MS Excel - Cumulative sum of a column based on “id values” in another column?

I have been trying to figure out how to make a formula that calculates cumulative sums of rows in a column based on their id values in another column but still haven't found any solution for it. I try ...

**0**

votes

**0**answers

11 views

### Dynamic Values in a formula? [migrated]

I have a google sheet where I am using this query to pull data from another sheet :
=QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...

**-4**

votes

**0**answers

12 views

### To get the result in excel [closed]

I have few character with some gap in each charaters.
Just wanted to get all the characters before the gap
Eg:- TSFT40009 TRMG23789
MNCDFEWIPZY MNKYUNIM20.