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

learn more… | top users | synonyms (5)

1
vote
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
2answers
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
3answers
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
3answers
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
2answers
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
1answer
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
0answers
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
1answer
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
2answers
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