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

learn more… | top users | synonyms (5)

0
votes
1answer
15 views

Excel formula - is it possible to return a value adjacent to a list of values, depending if that list of values matches another list?

In Sheet 1, I have a list of document numbers (alphanumeric) in Column A, which have a corresponding title listed in Column B. I need to compare another list of document numbers (alphanumeric), ...
0
votes
1answer
16 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
1answer
10 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 ...
0
votes
0answers
7 views

How to consolidate information from different sources in Google Sheets [migrated]

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
0answers
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
2answers
29 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
0answers
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
2answers
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
0answers
20 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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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 ...
0
votes
1answer
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
0answers
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
18 views

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

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