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

**1**

vote

**0**answers

142 views

### Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...

**0**

votes

**1**answer

141 views

### Move every first line of a pair of cells in a column to the same row to the left of the second line

How to move every first line (digits) of a pair of cells in a column to the same row to the left of the second line (names)? The pairs of cells are at a random distance in this column.
0117-000-00
...

**-1**

votes

**1**answer

98 views

### Create an event in excel - add value to cell X after input on cell Y -> clean cell Y

Basically, what i want to do is:
Whenever i insert values in column B, it add the value to column A and then sets the column B to 0.
A B
23 0
I tried this function(passing A as uni and B as ...

**0**

votes

**1**answer

1k views

### How can I get the row number from a vlookup?

So I have two spreadsheets where there is some messy data in one of them that needs to be in another.
What I have done so far is copy the columns I want from the messy spreadsheet into a new tab on ...

**0**

votes

**2**answers

65 views

### formula writing for excel if/then

Column D is a list of states. There are 3000 lines.
If column D is AL, GA, SC, FL, NC, TN, or MS then I want column G to say to say GA.
If column D is LA, TX, NM, OK, AR or NE column G should show ...

**2**

votes

**1**answer

1k views

### Excel: Reverse VLOOKUP with comparaison

I have an original table (the value column is sorted):
name | id | value
--------+-------+-------
abc | 1 | 12
def | 2 | 15
ghi | 3 | 23
jkl | 4 | 29
...

**1**

vote

**1**answer

63 views

### Excel fomularbar shows correct Value, Sheet shows VALUE error

Here is the data I have been using, data format all General:
Name Address
Adam 250
Mohame 150
Adam 220
Adam 423
Yasir 240
Ibrahim 236
Adam 242
Index formula cell B11 contains the ...

**0**

votes

**1**answer

34 views

### Transpose data by checking similar cell data

I have a Excel sheet like below..
Color Size Qty
Red L 182
Red M 223
Red S 155
Red XS 30
Blue L 700
Blue M 845
Blue S 665
Blue ...

**3**

votes

**1**answer

165 views

### Input the same formula in excel across multiple worksheets to follow a sequence

I need to put the same Excel formula in Excel so that it follows on in the sequence. So sheet 1 needs to have ='Booking Sheet'!F1 then sheet 2 ='Booking Sheet'!F2 and so on.
Is there a way I can do ...

**0**

votes

**2**answers

1k views

### Parse data in excel — space delimited — right to left

1 Exxon Mobil 452,926.0 41,060.0
2 Wal-Mart Stores 446,950.0 15,699.0
3 Chevron 245,621.0 26,895.0
4 ConocoPhillips 237,272.0 12,436.0
5 General ...

**6**

votes

**2**answers

11k views

### Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value
For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...

**2**

votes

**2**answers

3k views

### Excel reference named range with text and formula

Is it possible to refer to a named range by combining text with a formula? Say I get a name for a range via INDEX which returns a text but I also want to add something after the text like so:
...

**0**

votes

**2**answers

1k views

### How do I mark a cell if another cell contains data from a list in Excel?

I am working on a spreadsheet and I need to have a way to mark items that have been recently ordered.
Each row is an item in inventory. I have a separate list of item numbers that have been recently ...

**2**

votes

**2**answers

417 views

### Excel - How to build pick lists dynamically

Here's worksheet A:
Parent Kid Age
------ --- ---
John Scott 5
John Lucas 7
John Elisabeth 12
Victoria Jason 3
Victoria Amy 5
Jenifer ...

**1**

vote

**1**answer

111 views

### Transposing columnar record data to rows in Excel

Can I reference a cell in another worksheet like this?
Example:
Worksheet A
A1 Data1
A2
A3 Data2
A4
A5 Data3
Worksheet B (I want to copy by referencing the cells in Worksheet A)
B1 ...

**0**

votes

**2**answers

280 views

### Excel: Use of spaces in filenames/sheetnames with INDIRECT()

Formula is:
=INDIRECT("'Z:\ADMIN PROJECTS\[FORWARD ORDERS.xlsx]"&A1&"'!C$2")
Value of A1 is SEPT 13 in text (to be replaced by another formula soon).
Excel keeps giving me #REF. I'm pretty ...

**1**

vote

**1**answer

114 views

### How can I use excel to create a multiple IF function?

I have a spreadsheet comparing numbers of programmes sold by my company. I want to create a formula that will total several variables.
In one column I have the month then whether it is a "Finance" ...

**1**

vote

**1**answer

264 views

### Displaying Other Values Via COUNTIF in Excel

Basically I have a sheet like this:
A B C D
Name (From File 1) Name (From File 2) Phone Number Countif
Walt ...

**1**

vote

**2**answers

2k views

### Filter for first unique instance a column in Excel?

How can I filter for unique values in a particular column (disregarding the values in the other columns for that row, and so the first instance of a new value would be fine)?
So, for example:
1 ...

**0**

votes

**1**answer

183 views

### How to format text with multiple periods in Excel?

I have text with numbers that I need to format in Excel. The format I tried using in excel is 00000.0000.0, which would work beautifully if it weren't for the double periods.
19271.23.8 -> ...

**0**

votes

**1**answer

9k views

### Excel Multiple Cells Included in an IF Formula

I am trying to compare 6 cells, and get a pass or fail at the end, but in Excel it looks like it will only let me have 5 cells. I'm not sure how to word it.
like for example:
Pseudocode: if (A1 and ...

**2**

votes

**2**answers

15k views

### Conditionally format cells based on match in another sheet

I have an Excel spreadsheet with 2 worksheets. The first is just a header row and a single column of item names. The second is a list of item groups, with a header row and a title in the left most ...

**0**

votes

**1**answer

2k views

### Excel - can't get the formula to copy correctly

Have an excel sheet.
I want B3 = C3 * C2
So far everything is good.
I want B4 = C4 * C2
Did these two manually.
I have a lot of rows so I wanna copy the formula.
The problem is, I select them ...

**1**

vote

**1**answer

286 views

### Make a system call to calculate a spreadsheet cell value

Is it possible for me to make a system call in order to calculate a cell value in LibreOffice Calc?
To clarify, can I set a cell's formula to something like this:
=B3*C4-system("/my/amazing/script ...

**1**

vote

**1**answer

38 views

### Adding table values between an undefined list of worksheets that keep data key associations

Let's imagine you have a totals worksheet and 5 other worksheets that consist of the data for the totals worksheet. Lets also say every worksheet has the same table structure defined on it. For ...

**0**

votes

**2**answers

80 views

### Spreadsheet formula to list all unique entries EXCEPT for certain cells in a column

Ok, so I have 3 columns that look roughly like this:
Name Paid To
======== ======== ========
John 10 Roy
Jane 50 ...

**0**

votes

**3**answers

491 views

### excel: how to *clone* the formula to another cell?

suppose cell A1 has formula "=B1+C1", if B1 is 2 and C1 is 3, A1's value will be 5.
Now, I'd like to "clone" cell A1 to B4, how could i do that?
If I just copy and paste, cell B4's formula would ...

**0**

votes

**1**answer

70 views

### Summing Column Without Circular Reference

I have a column of 5 rows that is used to calculate whether the item to its left (each row corresponds to an item on an invoice) qualifies for a discount. Each cell in the column applies the discount ...

**0**

votes

**1**answer

78 views

### How to convert 58m32s to 00:58:32 or 72m10s to 01:12:10

I have a excel column with data like:
58m32s
72m10s
125m02s
And would like to show it like:
00:58:32
01:12:10
02:05:02
What formula combination should i use to make this possible? This should(?) ...

**2**

votes

**1**answer

361 views

### Concat of 2 excel cells of formatted text which can keep the formatting?

I would like to have a cell (A1) that is the exact concatonation of two source text cells (A2 and A3). The two source cells are simple text with SOME of the text in bold, underlined and of different ...

**1**

vote

**1**answer

424 views

### Finding a Zero Value in one column and the corresponding value in 5th column from previous row

I have a table that has 5 columns. The rows after some point will have a value of zero. I need to get the value of the previous row of column 5.
For example; here after 4th row, all the value become ...

**1**

vote

**1**answer

359 views

### Percent Rank and Curved Grading in Excel

I am trying to design a gradebook for a class which follows relative grading. Relative grading here means 30% As, 40% Bs, 30% C and lower. In a class of 11, for example, 3 students will get As, 5 ...

**3**

votes

**1**answer

269 views

### Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value
A1:"AB 0123"
and in cell B1 I need the same value with a line break instead of the space like
B1:"AB\n0123".
How can I archive this?
I ...

**0**

votes

**1**answer

24 views

### In one end of table cell, concat all text from Cell A when Cell B is blank

I've got a spreadsheet of potential expenses in Numbers.app.
Text String | $Price
In some rows, the $Price cell is empty.
I'd like to iterate over all the rows, and when $Price is empty, CONCAT ...

**0**

votes

**1**answer

49 views

### Excel Date Manipulation Function

I have an excel table that contains start times and end times and the added hours. Here is what it looks like
***********************************
* Start Time ** End Time ** Hours *
...

**2**

votes

**3**answers

2k views

### How do I count # of rows in an array ONLY IF the sum of numbers for each row is >0 in Excel?

I've got a 150 column x 360 row array with random numbers (say A2 to ET361) in Excel.
How do I calculate for each column (i.e., from cell B1 to ET1) how many rows are greater than zero for the ...

**0**

votes

**3**answers

231 views

### Multiple conclusions(formulas) in one cell

Let's say I have a table with the values:
0
100
200
300
In the next coloumn I have data entry cell of 250.
Underneath the 250 I would like a formula that says:
If 250 is less than 100 but more ...

**1**

vote

**2**answers

856 views

### edit excel formula changes cell not allowing formula to run

I am trying to put formulas into excel (2010 & not a new concept for me), however when i go to edit the formula the cell changes and only shows me the formula (not as a formula but as text in the ...

**1**

vote

**1**answer

91 views

### Following Sunday based on date in LibreOffice Calc [duplicate]

I'm looking to create a calculation to display the following Sunday in day-month format based on the current date (=NOW()).
How can I do that?

**0**

votes

**0**answers

30 views

### Excel String compare [duplicate]

I am trying to compare string and then display the what doesn't match between the two of them, for example:
take A1 which contains [alpha:beta] and then B1 which contains [alpha] and it will match ...

**0**

votes

**1**answer

433 views

### Copy formula with changing references

Need your help because I am not sure of the correct formula to use (if ever there is one!) in Excel 2010.
Say in column A, I have my figures (A1 to A1000). Suppose in column B I need to get the ...

**0**

votes

**2**answers

227 views

### Excel calculate sum range with other calculations alongside

Alright so I have an Excel sheet where I store certain numeric data. Basically I have a column headings and date as line headings. The data is numbers in between:
column 1 | column 2 | ...

**1**

vote

**2**answers

85 views

### In Excel 2007, I need to convert a phone number to a serial number, preserving the last 4 digits

Example: Assume the device phone number is 847-630-4459.
Step 1: We add a “1” to the front of the number yielding: 1-847-630-4459
Step 2: For the first 7 digits (1-847-630), we subtract each from ...

**0**

votes

**1**answer

82 views

### Using the contents of a cell to refer to a worksheet name in a formula

I have the names of dealers along with the associated data located in various tabs in a particular workbook. I wrote a sumif formula for one and included the names of the dealers in the cell to the ...

**0**

votes

**1**answer

29 views

### Search the corresponding cell in a worksheet and input an entry

I did like to know how to search a cell based on a date entry,that serves as a holidays list. For example Christmas:25-12-2013.
Can you give me VB program that will address the 12th row and 25th ...

**0**

votes

**1**answer

98 views

### Sum of rows conditional on two conditions [duplicate]

I'd like to use array function to get conditional sum based on two columns. Given the example, for each ID I want to calculate the sum of value column if column B is -1
ID | B | value |
...

**0**

votes

**2**answers

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

**0**

votes

**2**answers

2k views

### Calculate different discount levels in Excel

I have to create a price list with 3 different discount levels called eg. E1, E2, E3.
E1 = 10%, E2 = 20% and E3 =30% discount.
How do I write a formula to tell Excel if the item falls in class E1 it ...

**0**

votes

**3**answers

71 views

### How can I get the string of a cell?

I have a list of around 20 items and the user can pick three of them to perform a comparison, so I need a formula which gets the "TRUE" and gives me the contents of the correspondent cell in the same ...

**1**

vote

**2**answers

59 views

### sum sign in number cells

In 2 cells, I exactly have these values:
A1: 1+2+3
A2: 2+0.5+10
Is it possible to have sum of these cells (=18.5) in another cell?
I tried =SUM(A1:A2), but it returns 0