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

learn more… | top users | synonyms (5)

0
votes
0answers
4 views

Google spreadsheet formula error

So I have this formula: =CONCAT(=IF(F26 = " Kb","0",IF(F26 = "16 Kb","16",IF(F26 = "64 Kb","64")))) Im trying to return the result "0" or "16" or "64". Three possible inputs, and three utputs. This ...
1
vote
2answers
35 views

Comparing two strings in two columns in Excel Sheet

Can anybody help me to correct this data? There is a long list of this type of data: If there are columns that are exactly matched, like in row 5 (Harley- harley) it should return 1 or 'true' in ...
0
votes
0answers
9 views

Excel changes the total sum of a column when changing the row order

I have a large list of numbers, in a different cell I have the formula =SUM(G:G). I need to work with the data on the list for different purposes but when I change the order of the rows, my total on ...
0
votes
3answers
3k views

Excel saves a file in .csv but it keeps the columns and the rows as they were in the original sheet

I have a problem converting an Excel sheet in a *.csv file. I've searched all day for solutions, but nothing seems to work. After I select the sheet and save it as "CSV(Comma delimited)", pressing OK ...
0
votes
2answers
48 views

Excel: What can cause manual calculation / F9 to stop recalculating?

What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ? I've Googled this problem and ruled out all the explanations I can find: Calculation settings are ...
0
votes
1answer
20 views

Creation of an IF formula that will return a 1 or 0 for times between a range

I am trying to create a spreadsheet that will calculate total number of patients per hour. I have an arrival time and a discharge time. What I was thinking is to organize my spreadsheet with times on ...
0
votes
1answer
32 views

Sum multiple items with multiple criteria

Given the following table, let's say I want to sum the New York "ACT" sales in Q2. The answer would be 59. How do I do that with a formula?
0
votes
3answers
24 views

I need one cell to reference different cells based on another call's value

I hope I explain this correctly. I am looking for a formula that allows one call to reference a specific cell based on the value of another cell. For instance If cell A1=10 (I manually put in this ...
0
votes
0answers
34 views

Create an atypical number pattern in Excel

I would like to create a custom list in Excel that repeats a number pattern, that I can fill automatically like I do with other custom lists in the Advanced Options. The pattern uses numerals 1-7 in ...
-3
votes
2answers
62 views

Excel date formula

Could someone look at my formula and assist me with a resolution? The formula must have an error since my amount is going into column AD instead of AC like it should. See highlighted cell. For ...
3
votes
1answer
37 views

Sorting data without mixing rows

I have three sheets. First sheet renamed General: Dave Henderson 21/05/2014 Elton Rus 22/06/2014 John Smith 20/05/2015 Second sheet renamed ...
7
votes
4answers
52k views

Dynamically reference a Named Table Column (via cell content) in Excel

How do I reference an Excel table column dynamically in Excel 2007? I want to reference a named column of a named table and the referenced column will vary with the value of a cell. I have a table in ...
1
vote
4answers
37k views

Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...
0
votes
2answers
171 views

Why does excel tables break VLOOKUP formulas

I have a worksheet using raw data that Excel does not identify as Tables (ie. plainly putting values in adjacent cells), and on another part of my worksheet, formulas doing lookup in that data, like ...
1
vote
1answer
14 views

LibreOffice Calc: how to pad number to fixed decimals when used within formula?

In a LibreOffice spreadsheet, I want to display numbers (actually, prices) with two decimals. When a cell contains just a number, I can format it to do automatic padding. But sometimes I have cases ...
0
votes
1answer
32 views

Return Non Null Cookies And Count

I have two worksheets in my workbook. The first worksheet is titled SalesPerson and should show a rundown of the salesman that is entered in cell B1 and the type & quantity of cookies sold. ...
-1
votes
0answers
9 views

Conditional format — copy format of another cell (Google Sheets) [migrated]

I'm using Google Sheets spreadsheet, hope it's ok to ask about it here (in superuser). Chances are the methods to accomplish this might be different from Excel or LibreOffice, if it can be done at ...
-1
votes
0answers
7 views

Google sheets: Automatically including new rows on separate sheet

I have a google sheet document with two separate sheets, Sheet1 and Sheet2. I want column A on both sheets to stay the same. Column A of S2 simply has ='Sheet1'!A2 for cells A2 on down. Let's call ...
0
votes
0answers
4 views

SharePoint data connection to Excel - “refresh all” turns all formula results to zero

I have a SharePoint 2013 list that is connected to an Excel spreadsheet. Data comes into the Excel sheet on the first tab. I have formulas on the second Excel tab (specifically, SUMIFS-based ...
0
votes
1answer
28 views

Excel search for substring and display count

This is an extended question from this one. Suppose now I have a following table with strings: A 1 CHI111 2 SEA222 3 SEA333 4 LA444 ... I also plan to have a table to keep count of ...
-2
votes
1answer
27 views

VBA Macro help needed [on hold]

I have two files , each file i have a column called subsystems. i need to check if my file A subsystems are there in File B.. this i can do with the help of Vlookup, but the in each single cell i have ...
0
votes
1answer
25 views

Trying to create a SUMIF or SUMPRODUCT with one positive criteria and one negative criteria

I need to perform a SUMIF, SUMPRODUCT, or similar formula with TWO criteria, one a positive match and the other a negative match. Refer to the very shady table below: A........| B.............| ...
0
votes
1answer
18 views

Excel - Reference cells with same value and SUM

The excel data sheet I have takes this form: XGTI, 10 XGTI, 12 BIOC, 5 BIOC, 5 BIOC, 1 BIOC, 10 XGTI, 5 BIOC, 1 MGT, 2 MGT, 4 I would like to total all the values separately from each other. e.g. ...
0
votes
1answer
16 views

Excel search for string in multiple cells, compare and display string with most counts

Excel 2013 I have a table as follow: A 1 CHI 2 SEA 3 SEA 4 LA ... How do I write a formula that can check cells A1 ~ A4 and display on another cell the string with most count? In this ...
0
votes
1answer
23 views

Excel Formula that autofill an array with a list, based on an array

I'm trying to do a formula that within a range makes a list based on info on a specific array. What I need is an array that aufills this list based on the following conditions: 1)It has to be the ...
0
votes
1answer
25 views

How Can I make an excel column of Hyperlinks open in NON default browser

I have a ms excel 2007 worksheet with a column (A) of hyperlinks using the excel function in this way =HYPERLINK(A2,"url". How Can I make (if this is possible) open all cels on that column in the NON ...
1
vote
1answer
28 views

SUMIF based on multiple criteria

I want to get the sum of a range, but I want to change how the sum us calculated based on critera. Say I have a table as follows Name | Value DP | 2 DP + BC | 4 DP | 4 DP + BC | 6 So ...
1
vote
3answers
4k views

Display multiple cells if data on another workbook contains “X”value

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...
1
vote
3answers
72 views

Excel - How to return the first number only string that is between 7-10 characters long in a large body of text? (preferably Formulas only)

I'm trying to extract some numbers from a few cells that each have a large body of text. The number strings are accompanied by certain words that precede the number string I'm trying to extract. ...
0
votes
0answers
38 views

Excel 2010 - Combine specific sheet in >200 separate files into single worksheet?

I have tried solutions in other posts like this and so far nothing has worked :/ I have >200 separate excel files, and essentially I would like a specific sheet from each of these files combined into ...
4
votes
2answers
126 views

Sort by Formula (without VBA)

I am attempting to sort a short column of values (descending) by using a formula in a different column. So if A1 through A10 contain numbers, in B1 I enter: =LARGE($A$1:$A$10,ROWS($1:1)) and copy ...
0
votes
1answer
38 views

Name in output column should be present only active member is “A” Else should check next row also now cell should be empty or Zero

I Have two workbook one have data as shown in image Sheet 1 and second workbook is output sheet2 The problem is I want data in output sheet from sheet 1 only if active column in sheet 1 Have " A" ...
0
votes
1answer
63 views

Excel formula to find lowest value above a given number and return the column name

I'm trying to write an Excel formula that will look at 5 columns of data (for example, labeled SEL1, SEL2, SEL3, SEL4, and SEL5) and return the column name of the lowest column with a value above a ...
0
votes
2answers
3k views

Lookup Multiple Results for Multiple Criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...
2
votes
2answers
210 views

Excel vlookup reference purposely missing shows #N/A - stop it

I have a cell which does a vlookup. But the table to which it refers is always changing and when the specific value is there is shows fine. But when the value isn't there it shows #N/A - how can I ...
0
votes
1answer
29 views

Formula to reference a cell value that contains text and retain the cell refences formatting

I have a bunch of cells containing text. The text in those cells have multiple font sizes and styles. I need to replicated those cells in a different order further down the worksheet and if I make ...
-1
votes
2answers
23 views

Sum with multiple criteria and vlookup from another table

In the table below, I want to sum all of New York ACT in Q2 after the sales have been multiplied by the corresponding rates. Is there a one-shot formula I can use without needing to multiply all the ...
1
vote
1answer
22 views

Calculating percentage change over a range of 5 cells where a different two will be populated on each row

I have 5 columns for 5 consecutive years. I need to calculate the percentage change between the first value present across the column range and the second value present. The other three columns will ...
-1
votes
1answer
28 views

advanced if excel formula

I want to write an excel formula that calculates cell x divided by cell y, but if the resulting value is between 0 and -.3 return a value of 0, but if less than.-.3 then return the inverse square of ...
1
vote
1answer
3k views

VBA: How to replace entire Excel cell value basing on its content?

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell. For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", ...
0
votes
2answers
7k views

Count from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...
2
votes
4answers
178k views

How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database?

I have a few dozen excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets. The ...
-3
votes
0answers
21 views

Need a chart type to display turn around time for code requests received during the month [closed]

I need to display on the dashboard the following details every month need some idea on how to plot the data and use the best fit chart for this data Data to report - Number of EAP and EDG requests ...
0
votes
1answer
136 views

Apply a function/formula to a range in excel

I have many operations to perform on a set of data in excel. The data is in columns, and the functions are a lot of variations on things like =(A1*C1/B1)+(A2*C2/B2)+... Right now they are spelled ...
0
votes
2answers
3k views

Defining Variables in Excel

I have two lists of data, x and y, one in a row one in a column. I would like to populate a chart of rows and columns with a complex formula that is the same for each chart point, the only difference ...
0
votes
1answer
23 views

Sum of specific cells, up to a given amount

I am attempting to create an overflow spreadsheet using formulas. The "Starting Petty Cash" (on the right) is the carryover from the previous term, and is manually entered. The "Count", just above ...
0
votes
1answer
36 views

Data validation to give text a value that's used in a formula elsewhere on the sheet

We have a group planning to charter a sailboat as a vacation for 10 days. I'm trying to show the total costs of the trip. It's a simple spreadsheet listing many semi-fixed costs like food, beer, ...
0
votes
1answer
20 views

Use SUBSTITUTE formula to remove text from string in Excel, with text to remove being referenced from another cell

I have the following sheet setup Column A - Brand Names Column B - Product Names The product names are structured as: Nike Air Force White Addidas Trainers Black I want to remove the brand name ...
0
votes
1answer
3k views

Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...
0
votes
3answers
5k views

Excel sheet becomes very slow

I am working on an Excel 2007 workbook (less than 1gb in size). There is one sheet which has formulae linked to other two sheets in the same workbook. The problem is that it becomes really slow ...