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

**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

**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

**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

**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

**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 ...

**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 ...

**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 ...

**2**

votes

**0**answers

18 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 ...

**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 ...

**-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 ...

**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**

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 ...

**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 ...

**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 ...

**-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

**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 ...

**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 ...

**-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 ...

**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 ...

**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 ...

**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 ...

**-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.

**0**

votes

**1**answer

20 views

### Grab cell data and use in “IF” statement

Is it possible to grab a cell containing a date and paste that date in an "IF" statement containing text, similar to the below:
...

**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

**1**answer

33 views

### SUMIFS w/ multiple Criteria

Base on the Emp ID and the Pay End date I'm looking to sum up the Total Earnings and if it is equal to or higher than the WC Cap in the exposure column I'd like to return the WC Cap value. If it's ...

**0**

votes

**0**answers

32 views

### How to Sum based on two critera, date range and name

I have a table that has timesheet data for a number of users.
Column A has the resource Names. Row 1 contains each week of the year, starting with the 5th of Jan.
The table contains hours booked by ...

**0**

votes

**0**answers

25 views

### finding the difference between two times in libreoffice

I'd like to know how to solve the problem:
in cell A(n) is the first time
in cell B(n) is the second time
in cell C(n) is difference of times
I don't want to put each time into cell C(n) function ...

**0**

votes

**1**answer

31 views

### Conditional sum in Excel or Libreoffice calc

As an example, in the table above, I want to populate the empty column titled 'Totals' with the corresponding sum of all the Values in the left tables 'Values' column belonging to the category ...

**0**

votes

**1**answer

16 views

### Converting a column list from A2:A142 to a list with commas into cell B1; holding a format as “0000” with leading zeros

This code works to convert the column list to a concatenated list with commas, however the format doesn't have leading zeros while the VBA loops in B!.
Dim i As Integer
Dim s As String
i = 2
Do ...

**1**

vote

**1**answer

19 views

### Splitting Large Strings in Excel an Arbitrary Number of Times

Suppose I have a 50 character string and I want to split it in to arbitrary chunks. For example I might want to split it into chunks of 4,5,3,8,2... and so on. What's the easiest way to do this in ...

**0**

votes

**2**answers

32 views

### Use INDEX to lookup multiple values in a list

I am using this formula to Look up all instances of CUCUMBERS in column B on the first sheet of my workbook: "ITS" I have identical to column B in Column X.
So far this works great, just as I want ...

**0**

votes

**2**answers

30 views

### Google Docs / Excel how to transform redundant row in columns

I have a list of redundant rows like this
See my Google sheet
Target FRBBED05517
Job state EXECUTION_ORDERED
Target FRBBED05523
Job state EXECUTION_ERROR
Target ...

**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 ...

**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

28 views

### EXCEL: Additional COUNTIFS / Condition Needed for Value Ranges

I am recording multiple channels from individually housed birds, and sometimes their vocalizations bleed-through to other channels.
I want them to be tagged for removal in some way. ...

**1**

vote

**2**answers

75 views

### How to convert a number for a salary amount, to a specific, 9 digit, format in Excel

If I enter a salary dollar amount as 34512.15, I'd like to have a formula return this amount in a specific format as follows:
M(HT)(TT)(TH)HTOCC
M=Millions
(HT)=Hundred Thousands
(TT)=Ten Thousands
...

**-1**

votes

**1**answer

23 views

### Why doesn´t Excel function change cells for calcuation

I have the following function:
=SUMIF('Pivot'!$A$5:$A$51;'Projects Overview'!E4;'Pivot'!$B$5:$B$51)
I need it to automatically change $B$5:$B$51 to $C$5:$C$51, $D$5:$D$51, etc. when dragging the ...

**0**

votes

**1**answer

66 views

### How to use Index and Match formulas for multiple criteria

I have a spreadsheet that contains Olympic information, with the following fields in columns:
A) Athlete, B) Age, C) Country, D) Year, E) Closing Ceremony Date, F) Sport G) Gold Medals.
I want ...

**1**

vote

**1**answer

25 views

### Getting CHAR function to return multiple characters in Excel 2011

I'm using Office 2011 on Mac.
I'm trying to write a formula to strip lower-case characters (and ideally spaces as well, but that's less important) from text in a cell. The purpose is to convert ...

**1**

vote

**1**answer

32 views

### In Excel, How can I add values in a specific column if I have a checkbox type spreadsheet?

The Excel sheet is linear with column B being the distance walked each day. Across are a column for each participant. As they join the walking group each day, I'd like to keep a count on total ...

**0**

votes

**2**answers

30 views

### SUMPRODUCT with VLOOKUP

I'm trying to get the Product and Sum of what matches in the TC Ref column and the Earn Type.
For example 7926 would be (45.32 x -3) + (45.32 x -3) + (45.32 x -4) for REG and (45.32 x -4) for OT.
I ...

**1**

vote

**0**answers

19 views

### How do I incorporate IFERROR into this formula?

I need to know how to include an IFERROR statement in this formula in Excel 2007.
=VLOOKUP($E3,HSA!$E$3:$G$20,2,FALSE)*$D3
I want the formula to return a 0 instead of an #N/A.

**1**

vote

**2**answers

29 views

### How do I compare two list of data that contains a certain number in Excell?

How do I compare two lists of columns that contains a certain number? Please look at my example below:
Column 1 Column 2
-------------------
300 Room 305
301 Room 306
302 Room ...

**0**

votes

**1**answer

16 views

### How can I create multiple spreadsheets using the value of a column?

I have a workbook (Req_PO_Log_V2.xls) that has all of the expenditures for our repair and maintenance funds from our current FY. The lay out is as follows:
| Date | Req Number | Acct | Vendor # | ...