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

**0**

votes

**0**answers

36 views

### Prevent Excel from changing formula of last row of table when adding a new row

I have a table with columns named "date", "cost" and "daily". "Date" is a date, "cost" is a number and "daily" is a formula calculating the total cost of each day. The formula looks like this:
...

**1**

vote

**2**answers

46 views

### Excel '10 Formula to find & sum a row based on the intersection of 2 data points. (ie. Name(row), Month(column)

I need an Excel 2010 array formula that will take 2 given data points, say a "Name" (row) and "Month" (column), and return the sum of a row based upon the intersection of these two points within a ...

**0**

votes

**4**answers

47 views

### Getting last cell in row with data and corresponding data in column A in Excel

I have a spreadsheet that in column A lists dates. Column B may or may not have data in each cell.
The question I have is when I grab the last cell with data in column B, how can I also grab the ...

**2**

votes

**2**answers

33 views

### How can I get the relative position of a cell in a range?

I have a range of cells in a workbook, and I want to dynamically replicate them in another workbook. I don't want to use explicit cell references in case the range is moved, I always try to use named ...

**2**

votes

**2**answers

160 views

### How to concatenate values in Excel?

I have a spreadsheet that looks something like this:
A B C D E ...
1| 1 0 0 1 0
2| 0 1 0 0 1
3| 0 0 0 1 0
4| 1 0 0 0 0
I'd like to concatenate row 1 in column F so ...

**0**

votes

**1**answer

20 views

### Excel: How to give cells chosen by some criteria as a parameter in Variance (or any other) formula?

For the following Excel table:
A B C
1 Score Category VariancePerCategory(Calculated)
2 0.3146 Orange 0.034214
3 0.5711 ...

**0**

votes

**1**answer

100 views

### Formula to update cell based on user changing another cell's content

I want to know if there is a formula to update a cell in Excel with today's date
when a user changes any value in the row that contains that cell
OR
when a user changes the value of 6 / 7 / 8 ...

**0**

votes

**1**answer

39 views

### Count filled cells in row, given from adjacent column not being empty in Excel 2010,

I have a two worksheets, one in which I have a list of tasks as well as several other columns listing the status of the task and one column listing if the task is imortant or not. In the other sheet, ...

**0**

votes

**1**answer

21 views

### How to search a range of cells for specific txt, then copy to adjacent cell

I have a range of cells, e.g. g1-g1000 that contains two type of entries: xx.xx or xx.xxCR, where xx.xx are numbers.
I want to search the range of cells for xx.xxCR and when a cell is found, copy ...

**0**

votes

**0**answers

29 views

### Delineate Excel Formula Without Using Wizards

Is there a combination of excel formulas that I could use to do this without using VBA or the import wizard? I know how to insert comma delimited data from a text file and to split a cell into ...

**0**

votes

**2**answers

46 views

### Compare lists and add missing

I have 8 Spreadsheets on one workbook. The first one is a weekly overall view. The other seven are broken into days. Each day lists some names of people that came on site. So Monday might have Joe, ...

**0**

votes

**1**answer

69 views

### Excel table: Add only certain values (Oscar predictions ballot)

So for tonight, me and friends made a spreadsheet with oscar nominations and predictions of winners. What I'm trying to achieve is that I just put a '1' or an 'x' or something behind the winner in ...

**0**

votes

**1**answer

24 views

### How to summarise costs for one week, given an irregular range of dates and costs?

I have a table of all the money I've spent on given dates, from receipts etc. The dates aren't regular, so the table may look something like:
A B
╔════════════╦════════╗
1║ Date ║ ...

**1**

vote

**2**answers

29 views

### How do I Calculate Variance with Missing Values coded as -99?

I am trying to calculate individuals' variances on multiple measures, however, if they skip a question it is coded as -99. I would like my formula to calculate the variance for these measures while ...

**1**

vote

**1**answer

38 views

### How to drag fill series in excel to adjust sequencing without removing the hyperlinks attached to cells?

I have a range of cells in a column in excel, that have some number in them along with text. For Example:
TA_47
TA_48
TA_49
TA_50
TA_51
TA_52
TA_53
TA_54
TA_55
TA_56
TA_57
TA_58
TA_59
TA_60
Now I ...

**0**

votes

**2**answers

39 views

### How do I write an IF Statement for Percentage Formulas?

How to I write an IF statement that if I divide two negative numbers, the results show a negative percentage and not a positive percentage?
Example:
(69,002)/(11,440) = 603.16%
I need for ...

**1**

vote

**1**answer

23 views

### Conditional Formatting Based on Prefix Character (')

In Excel there are prefix characters which appear before the cell contents. Usually this is a blank character and Excel formats based on its best guess. If an apostrophe is used it treats everything ...

**2**

votes

**1**answer

73 views

### Is it possible to automatically add cells from a new sheet to a formula in Excel?

I would like to make a new financial spreadsheet for tracking expenses. Since the expense sheets are created as and when I cannot pre-generate a spreadsheet to populate with formulae. I have a summary ...

**0**

votes

**1**answer

40 views

### How do i do a “tweaked” VLOOKUP?

So here is the problem: I have 3 lists (adjacent columns) in a spreadsheet.
The 1st list contains the values I am searching for
The 2nd list contains the first half of table array which I am ...

**0**

votes

**1**answer

42 views

### Excel: Converting two lists into a matrix

I have two lists in my workbook currently
Name | System 1 | System 2 | System 3 |
John | x | x | |
James| | x | x |
Peter| | x | ...

**1**

vote

**1**answer

21 views

### SUM Array using IF AND

I am trying to get a SUM function to check both the month and a check of Y or N to return a total.
Currently I am using the array formula =SUM(IF(MONTH(A8:A1000)=1,C8:C1000,0)) to do the month ...

**0**

votes

**1**answer

46 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

45 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

29 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

26 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

30 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

33 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

36 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

37 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

60 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

23 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

70 views

### How to set an excel cell's value programmatically without using VBA

I want to be able to change the value of a cell using a test conducted in a different cell. For instance, something like the following.
A B
1 do =IF(EXACT(A1,"do"), set(P5="it was ...

**0**

votes

**1**answer

30 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

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

**1**

vote

**2**answers

31 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

32 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

19 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

44 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

16 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

26 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

27 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

47 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

30 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

37 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

29 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

34 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

31 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

30 views

### in Excel 2013, I want to have the column letters automatically increment in a formula

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

34 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

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