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

**1**

vote

**1**answer

485 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

418 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

30 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

52 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

317 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

1k 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

102 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

31 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

589 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

284 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

100 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

97 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

33 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

106 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

**3**answers

3k 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

3k 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

72 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

**0**

votes

**2**answers

247 views

### MS Excel (2003) Is there a bug in DSUM or am I missing something blindingly obvious?

I am trying to check some data in an Excel database, and have created a separate sheet (in the same workbook) with what should be some fairly simple "DSUM" queries.
I have copied a formula (absolute ...

**0**

votes

**1**answer

244 views

### Numbers/Excel Hour Counter within Specified Date Range

The data shown in the image is just sample information but...
I would like to use the "Period Beginning" and "Period End" to create a range to determine the total number of hours and earnings. I ...

**0**

votes

**0**answers

610 views

### Create a Macro that searches, copies and Exports from two workbooks and imports into 1 master workbook

I am new to creating macros. I have tried searching for existing macros to help with my current request; however I have not found a helpful one. Please let me know if you can assist.
Contents
...

**2**

votes

**1**answer

230 views

### Filling content of cell based on adjacent cell

I have a column of data like column B below. The letter "A" denotes software, "B" a service and "C" hardware. How do I match each product with its category to make it look like the table below?
A ...

**0**

votes

**1**answer

4k views

### How to count the sum of distinct Excel values in a column?

After having searched Google & Co. for hours and trying various examples, I still could not figure out out how to solve what I thought would be trivial to calculate in Excel:
Having a table with ...

**0**

votes

**4**answers

508 views

### ITSUMIF Criteria for Cells That Don't Match a Condition

I have an Excel spreadsheet that looks like the following:
# |A |B
-----------------------------
1 |Expenses |Category
2 |$5 |Food
3 |$10 |Entertainment
4 |$10 ...

**0**

votes

**1**answer

499 views

### I neet to convert date value into date/time value in a cell

I have a cell with 07/26/13 and I want a formula to take this date each day it is updated and convert it to 07/26/13 00:00, so that I can make a formula in another cell with a time reference of IF ...

**1**

vote

**1**answer

2k views

### PivotTable — how to sum the result of a countif formula by group?

Current Setup
A table (Table1) that has the following fields:
Organization Name
Person Name
Several fields that users can put an "X" into (each represents a "module" that users would need access ...

**1**

vote

**2**answers

31 views

### Make a cell with text and a date into just a date

I have a cell (E2) that says "Manage Hours For 07-25-2013" I want to make a cell that adds a day to whatever day is in E2. How do I make that cell have only the date in the answer and not have "manage ...

**0**

votes

**2**answers

4k views

### Excel - Counta 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

**0**answers

3k views

### Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with
the font color red. All other rows are in the default font color.
Can I use the font color of a cell within a formula to react to ...

**0**

votes

**1**answer

1k views

### CONDITIONAL FORMATTING for First SIX characters, ALL duplicates Highlight

How do I use CONDITIONAL FORMATTING for the First SIX characters ? I want ANY and ALL duplicates of the First SIX Characters highlighted.
I need a formula but cannot figure out what formula to ...

**0**

votes

**2**answers

315 views

### How can I get the Max function to return a value in the winning cells column but not the winning cell itself?

1. Weeks 22 23 24
2. 3 7 1
3. 4% 9% 1%
4. 4% 2% 2%
For example in row 3, how would I get the MAX function to have 9% win but then return the number 23 a row ...

**2**

votes

**1**answer

174 views

### Sorting Common Values by Row in Excel

I have two sets of data from two years as below which needed to be sorted by product and row. Eg. I need A2 and its product sales on the same row for both years.I have had a look at the the solution ...

**1**

vote

**2**answers

658 views

### How do I drag down complex calculations in open office.

For example if A1 contains 30 and in A2 I write =A1-6 then I'll get 27 in A2 and if I click the little black squair in the lower right hand corner of A2 and drag it down then I get a column that looks ...

**0**

votes

**1**answer

8k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2).
My problem is the percent changes from -4 to +4.
A change ...

**0**

votes

**1**answer

108 views

### how do i get a simple IF function to work in Excel only when another cell has a value in it?

How do I get a simple IF function to work in Excel only when another cell has a value in it?
I've tried =IF(ISBLANK(G4)="Sold","Closed","Open") but that didn't work so I was wondering if anyone could ...

**2**

votes

**1**answer

283 views

### how to determine which values in a given set of cells will add up to a numerical range

In other words, I'm looking for a sum range that consists of combinations of cells from an array. Given a set of numbers:
1 720 56 17 59 120 153 203 39 1 690 583 582 561 256 310 232 95 108 16
26 ...

**0**

votes

**1**answer

453 views

### How do I get Copy & Paste to work like Cut & Paste with formulas in Excel 2007?

Excel 2007 when I Cut & Paste =K62 to a new worksheet and I get =Scenario!K62. When I copy and Paste it I get =REF!.
How can I get Copy & Paste to work the same way as Cut & Paste does?

**1**

vote

**2**answers

175 views

### Find unique values in a range that occur a minimum number of times

I am trying to cut down on some manual work I do by using Excel functions. Each day I have a list of statistics that I need to analyze, and all values vary in one way or another. Each spreadsheet has ...

**2**

votes

**2**answers

168 views

### How to create number +1 from the previous sheet

I want to copy single sheet to multiple sheets in the same workbook with macros,I know how to do it but I still have further question.
I am using formula:
Sub Button3_Click()
Dim x As Integer
Dim y ...

**0**

votes

**1**answer

737 views

### I need to have a formula that will choose the Least value out of 3 cells

I need to have a formula that will choose the least value out of 3 cells.
Cells are as follows with random numbers
I29=5
I30=10
I31=15
Currently I’m using
...

**2**

votes

**2**answers

73 views

### How to split contents of a single column into 2 columns

I'm using Excel 2003. I have a spreadsheet that currently has 500 rows of data and just one column (A). For example, the first five rows look like this:
A
1 loremipsum=fufu.com
2 ...

**0**

votes

**2**answers

160 views

### Excel specific cell conditional formatting

I am trying to format a whole column (A1:A100) based on the values on each row.
For example: If A1 > 0, then cell should be red
If A2 < 0, then cell cell should be green
And so on ...

**1**

vote

**1**answer

35 views

### Excel date issue

I have the formula =RANDBETWEEN(DATE(1930,1,3),NOW()-1) in a cell to give me a random date. I have the format of the cell set to a date so the random number looks like a date. For example 38562 looks ...

**0**

votes

**1**answer

897 views

### String match using values in one worksheet with long list (52K) of values from a column in another worksheet

In worksheet 1: I have a fairly short list (~75) of values (text strings) and would like to find all matches of these strings with values from a column in another worksheet (worksheet 2)... these ...

**-1**

votes

**1**answer

216 views

### How to fill Excel 2010 Cells with a recursion idea

What I'm trying to do is have cells auto populate and un-populate dependent on the value of another cell.
For instance, Cell B3 has a set value of -0.1, Cell B4 has a set value of 0. Cell D3 is the ...

**0**

votes

**1**answer

3k views

### Excel: COUNTIF for Filtered Data

I'm trying do a "COUNTIF" on a list of 24 items (a list of times, in number of hours).
For some background, the formulas :
=COUNTIF(H2:H25,7)
=COUNTIF(H2:H25,"<7")
return to me a list ...

**0**

votes

**2**answers

91 views

### Excel Equations for Spreadsheet

I have a excel spreadsheet, and while I like to think of myself as quite the excel wizard, this one has me stumped, it's probably something ridiculous but I really can't get it too work. I have a ...

**1**

vote

**1**answer

3k views

### Microsoft Excel cumulative calculation performance

I know 2 ways to calculate cumulative values in Excel.
1st method:
A B
Value Cumulative total
9 =A1
8 =B1+A2
7 =B2+A3
6 =B3+A4
2nd method:
A B
...

**0**

votes

**2**answers

345 views

### I wanted to count unique numbers in a excel and combine it with a countifs function

I have a data sheet with these specific columns (among others)
Date time (in one column)
Campaign name (I work at a call center) in another
Agent ID number in a third
What I am trying to do is pull ...