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

learn more… | top users | synonyms (5)

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