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

**0**

votes

**1**answer

76 views

### Excel Formula show with result as well as [closed]

I have a sheet with:
A B C
7 5 =A1+B1
So, C1=12.
Is it possible that D1 shows the numbers 5,7 used in the sum in C1?

**0**

votes

**1**answer

63 views

### Formula referencing different spreadsheet needs to output both text and number formats

So I have a formula that references a different spreadsheet:
=IF('Raw Data Import'!G2=""," ",'Raw Data Import'!G2)
which outputs text format. However, since this cell is used in a different ...

**0**

votes

**1**answer

442 views

### Formula for COUNTIFS excluding another column

I'm trying but failing to work out a formula in Excel 2007. I have an large area of data that isn't in a table format. The main columns I care about are: chapter title (text), number of tasks ...

**0**

votes

**1**answer

82 views

### Nested If Statements Without Invoking Circular Reference Error [closed]

I have a formula that calculates perfectly, except in one case near the end where it becomes negative. I was wondering if it would be possible to have the cell containing the formula say "If this ...

**0**

votes

**1**answer

189 views

### What's more efficient, array formulas or individual formulas? [closed]

Simple question:
When adding, multiplying, or otherwise doing simple operations on two ranges is it better to use array formulas or individual formulas?
=A1+B1
=A2+B2
=A3+B3
vs
{A1:A3 + B1:B3} ...

**0**

votes

**1**answer

243 views

### Combining Excel Formulas

I'm trying to do something on Excel which isn't working. I've managed 2 separate formulas that work separately but can't figure out how to combine them.
Separate formulas are:
=IF(K3>I3,"","Yes")
...

**0**

votes

**2**answers

266 views

### How to parse file datetime in Excel

I am trying to convert a text string like this - Dec 15 21:44 into a date/time format in excel (Polish version).
I tried =DATEVALUE function but without success.

**0**

votes

**0**answers

226 views

### copy and paste according to a order in a column

I want a macro that will fetch data from Series B and Automatically paste it according to the serial of Serial A. ASeries B and Series A data are same but Series B is not ordered. So i'm looking for ...

**2**

votes

**1**answer

149 views

### Why is my formula pulling the result CELL NAME and not the value of that cell?

I am creating a worksheet that compares two sets of data pulled on different dates. The column that everything pulls off of is created by an array formula comprised of combined unique values from the ...

**0**

votes

**1**answer

50 views

### If statement in excel 2007

I have a if statement in a cell that looks for the value in two different cells depending on which one is >0. Both target cells have a formula that selects a value based upon the value of the cell as ...

**0**

votes

**1**answer

139 views

### Excel IF Statement identify when cell has contents [duplicate]

How do I tell MS Excel to use the contents from one cell if data exists (this data can be positive negative or zero). If the data does not exist in said cell, use contents from another cell?

**0**

votes

**1**answer

205 views

### Combine GoogleSheets functions

I'm trying to combine multiple functions into one formula in GoogleSheets spreadsheet.
I've tried learning more from Google's Help center and through YouTube but didn't came to satisfactory result.
...

**0**

votes

**1**answer

29 views

### Excel 2013 time enter function

I want to enter hhmm and for it to appear as a time for example; 1233 12:33.
and 2245 as 22:45
Then I will need to plot these times on the x axis of a graph.
Any ideas?

**1**

vote

**2**answers

69 views

### How to create a product list with ONE owner, from a list of multiple owners?

I have a list of products in A2:A21 (A1:M1 is the titlebar). Behind these products are the names of 12 owners (B2:M21) who can repair these products. BUT... not all owners can repair every product. ...

**0**

votes

**2**answers

81 views

### In Excel, how do I get a list of cells to display based on an if function?

I have a list of quantities with a description column. It's counting doors based on size. I'd like to output a list (preferably separated by commas) of the description numbers with applicable numbers. ...

**2**

votes

**0**answers

37 views

### What function (or functions) can I use to pass worksheet title to a reference?

I have a number of cells on a worksheet that reference another worksheet. Pretty straight forward except I have to type the name of each worksheet in each formula which is becoming monotonous. What I ...

**-1**

votes

**2**answers

463 views

### Excel formula to calculate percentage with 3 options

I have a sales person on variable commission depending on amount sold. So basically I'm trying to put in a formula that gives me the answer to this all in one cell -
Sum of 1570 or under = nil
...

**0**

votes

**1**answer

29 views

### Excel: Using the TEXT function for spreadsheets used by users with different regional settings

I am using the TEXT function like =TEXT(B1, "ddd") where B1 is formated as date to display the weekday. This works fine on an english regional system.
On a machine with a german regional setting the ...

**1**

vote

**2**answers

88 views

### Is there a way to reference an external excel file depending on a cell's value?

I am trying to make a template for a document that will be updated every week, and I was wondering if there was a way to insert some values automatically depending on the value of a cell.
To explain, ...

**-4**

votes

**1**answer

362 views

### Consolidate Text Data in Excel

I have an Excel document containing exam results. The results are in one column and appear as Pass or Fail.
I want to know percent of candidates who passed.

**0**

votes

**1**answer

450 views

### Find Max Value in a Range - Return Contents of Cell in Corresponding Row

I have a spreadsheet that is made up of 2 columns - Each row contains a date and then a river gauge reading. The data covers 20 years - 1995-2015. I would like a formula that searches for the max ...

**-1**

votes

**1**answer

77 views

### Excel data validation: restrict to 10, 20 etc

I am trying to use Data Validation in Excel 2010. How can I restrict entry into a cell to numbers divisible by 10?

**4**

votes

**1**answer

55 views

### Formula bar view changes when document is saved and reopened

I have an Excel 2007 document with a lot of formulas of the format:
SUMIFS(view1[COLUMN1],view1[COLUMN2],$C13,view1[COLUMN3],I$10)
but when I save the sheet and reopen it each formula is changed to ...

**1**

vote

**1**answer

70 views

### Manually editing Excel worksheet, need my edits to save as written in .csv without reformatting back

Here is my issue:
I have exported about 2000 records from a database to manually clean up the dates attached to them. (It appears that someone exported these records before, then re-imported them ...

**0**

votes

**1**answer

92 views

### Can I use conditional formatting to format a cell based on color in the cell itself and the cell before?

Conditional formatting Excel 2010 how to format a cell based on color in the cell itself and the cell before.
I have with Conditional formatting marked a start cell and end cell with red I want to ...

**1**

vote

**1**answer

78 views

### Higlight a specific cell based on ADDRESS() return value

I want to give a style (highlight) a specific cell based on a date value. This date is, say, in cell
A1.
To achieve this, I thought I would calculate the address of the cell I want to style with the ...

**0**

votes

**1**answer

44 views

### How to have the column letters in a formula automatically increment

I have a table with multiple tabs.
On Tab "Reviewers", I have the reviewers' names in column A, and the items (documents) being reviewed in columns G through AG.
Each column contains the number of ...

**-1**

votes

**2**answers

40 views

### In MS Excel, what function should I use?

I have an original table:
Class 1 Class 2 Class 3 Class 4 Class 5
Ram Shyam Madhu Peter Xavier
Micheal Cynthia Helen Rama Hema
Peters Veronica Amy Sydney ...

**0**

votes

**1**answer

123 views

### How to use =sum function properly in excel 2007?

In the above picture, for the column G7, I wan sum of D7, E7, F7. So, I am using =sum(D7:F7), but excel is showing error for the formula by highlighting a green flag. If I ask it to correct the ...

**0**

votes

**1**answer

47 views

### link data from another .ods file

I'm using openoffice calc and I have 2 .ods files (complete.ods and summary.ods).
I have 1 cell from summary.ods which depends on complete.ods. I managed to get the value by using this formula:
...

**-2**

votes

**1**answer

256 views

### Excel - Need to get a figure from one worksheet to another (it's complicated) [duplicate]

I have an Excel workbook with 2 worksheets.
One worksheet is for the input of stock transactions, the other has an overview of the portfolio.
So on the portfolio worksheet I would like to have a ...

**1**

vote

**1**answer

74 views

### How do you create a bracket tournement sheet on excel [closed]

I am trying to create a bracket sheet that automatically moves a teams name to the next cell
example:
Team 1 in cell 'a1', Team 2 in cell 'a2'
The score for each team in cell:
'b1' for Team 1, 'b2' ...

**0**

votes

**1**answer

295 views

### How to find last row that has same value as my last one and output the number of that row?

I have a worksheet with stock transactions, so in column C I will input the name of the stock that I'm buying or selling at a given time.
.....A.....|....B....|...C....|
DATE | TYPE | STOCK
...

**0**

votes

**1**answer

56 views

### Hiding date in formula if nothing is entered

I want to have a Date forumla that adds 7 days to a date entered in another cell, but if nothing is entered I would like said cell to be blank
A1 if blank then have B1 blank
A1 is 2/1/2015 then have ...

**0**

votes

**1**answer

78 views

### MS Excel formulas displaying #REF when data is entered in a cell in a certain way?

I have various complected formulas pulling data from two pages (used as data entry pages), formatting it in specific ways, and displaying it on another third page (all in the same work book).
This ...

**2**

votes

**1**answer

221 views

### Excel Vlookup two conditions

Need to search in tables, found to exactly same lines in these two tables.
Between these two tables I have working VLookup search, but need to have condition "AND these columns are also equal on ...

**-3**

votes

**1**answer

64 views

### I need some help in excel validation [closed]

I'm not good in excel. I need to include an excel sheet in my page, and I need an expert who can write these custom formulas for cells validation.
1) A column that accepts only letters, no numbers or ...

**2**

votes

**1**answer

247 views

### How do I count multiple words available in sentences and multiple rows (countif, search)?

here is screenshot for the sheet.
https://flic.kr/p/qZb8ot
in column A there are some senteces list below:
A2. T-shirts, Varsity
A3. T-shirts, Sticker, Mug
A4. T-shirts
A5. T-shirts, Pin, ...

**2**

votes

**1**answer

705 views

### What would be the the mathematical equivalent of this excel formula? =PMT()

I need to create a javascript form that does the same calculation as this =PMT() function.
mortgage = 220000
rate= 4.75%
term = 30
The example formula I have is =PMT(4.75%/12,30*12,220000*-1)
What ...

**2**

votes

**2**answers

595 views

### Excel 2013 - Dynamically reference table by table name

The TLDR
I am looking for information on how to dynamically reference a table in a formula by name.
To be clear, I'm talking about an actual table object, not a named range, and I want to choose ...

**2**

votes

**0**answers

215 views

### Excel Index Match with IFs?? + options for next in list [closed]

OK I have been working on this for some time and can't quite get things right in the various combinations I have tried, so I am asking for advice.
I have a management data table where column C is a ...

**3**

votes

**1**answer

939 views

### Excel formula to get first and last non-empty value in a row and return column header

Let's say I have an Excel spreadsheet aggregating votes in some poll over a period of time as per the example below.
How do I find the min and max value people have voted on on a given day (the ...

**0**

votes

**1**answer

59 views

### How do I display name in column2 dependent on value in column1?

I am doing an inventory for all the businesses of a street. I have a two sheets, where on one I have different categories for commercial uses.
Each category displayed in sheet 1 has a number (1 to ...

**-1**

votes

**2**answers

196 views

### Select elements in an array that satisfy a criteria

The goal: Kappa-Sigma clipping in Excel
I want to implement a selective linear regression formula in Excel (avoiding VBA). I have a data sample on which I need to apply a linear regression. But my ...

**0**

votes

**2**answers

192 views

### Spreadsheet finding and sorting most frequent appearance of names

I have a spreadsheet that looks like the following:
https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing
There is a set list of names in Column A. ...

**0**

votes

**1**answer

167 views

### Searching for a value across 2 tables in Excel 2010, returning yes or no

I have one workbook with 3 separate worksheets
Sheet 1 contains 2 columns.
Column A has a range of unique alphanumeric identifiers.
Column B has a range of non-unique numbers.
A B
...

**0**

votes

**1**answer

59 views

### Frequency formula in Excel 2010

I am just trying to figure a way to get the frequency of occurrence of a number within a large array, not the freq of the number in the array belonging to a range.
Eg, how many times does the number ...

**0**

votes

**0**answers

187 views

### Excel automatically applying styles to new data

I'm having a problem with Excel 2013.
I have a spreadsheet filled with names and related information for applicants. When I contact the applicants, I highlight the cells (using the fill tool) so I ...

**2**

votes

**1**answer

651 views

### Excel averageifs multiple criteria on one range

I know how I can use average(if(... etc using an array, however I'm stuck on how to include other criteria in this
This is what I've got so far:
...

**1**

vote

**1**answer

406 views

### EXCEL - How to use two cells for input and output at the same time [duplicate]

I have 3 cells, VALUE, DOWNPAYMENT, DOWNPAYMENT %.
The 3rd cell "DOWNPAYMENT %" calculates the percentage of "DOWNPAYMENT" over "VALUE"
Is there a way for the user to have the function to input a ...