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

**0**

votes

**0**answers

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

**2**answers

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

**0**answers

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

**3**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**3**answers

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

**0**answers

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

**2**answers

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

**1**answer

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

**4**answers

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

**4**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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

**3**answers

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

**0**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**4**answers

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

**0**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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