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

learn more… | top users | synonyms (5)

1
vote
2answers
53 views

How to calculate values between three cells using conditional calculation

I am new to this forum and not very new to Excel (not in depth) I have to perform a calculation for three cells by conditional calculation. so here is the scenario Column Name: Total Area (Cell ...
0
votes
5answers
9k views

How do you add extra value to a cell that already has an existing number in it

I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...
2
votes
1answer
216 views

Capitalize first word in each row of a column in mac

I want to capitalize the first letter of every word in each row of a column. So I create a new column, type in =PROPER(B2), then drag that down the entire length of that column. It works great, it ...
1
vote
2answers
4k views

Look up the next highest value in another column with Excel

I have two columns A and B. A B 20 100 40 200 60 300 80 400 140 500 190 600 240 700 User inputs a value in a cell. I need to check ...
-2
votes
2answers
309 views

Nested IF formula

I have: in *Column F* in *Column S* F2 Monday S2 Orange F3 Tuesday S3 Mango F4 Wednesday S4 Grape F5 Thursday S5 Pawpaw F6 Friday ...
2
votes
2answers
950 views

Calculate average score for by category in Excel

Assume I have the following 2 sheets in an Excel workbook: Sheet companies: A B C 1 COMPANY COUNTRY SCORE 2 Foo Greece 10 3 Bar Greece 2 4 Baz Greece 3 5 ...
2
votes
3answers
5k views

Include an OR condition in a COUNTIF?

This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me. I have a column that can contain 0, 1 or ...
2
votes
1answer
224 views

Calculating mean of value across random trials/refreshes in Excel?

Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...
0
votes
1answer
1k views

In Excel I need to find data matches between two arrays - one horizontal and one vertical

In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...
5
votes
3answers
13k views

Check if Excel cell text is numeric using formula only

I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value. What I've found is that the ISNUMBER() function ...
0
votes
1answer
205 views

Issue with SUMPRODUCT Function

I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue: =SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5))) A B 1 4/1/2013 2 ...
0
votes
1answer
70 views

Formula to search array and return values

I have a Quotation Book and an Order Book each with a column called Q#. In the Quotation Book the quote numbers are generated and in the Order Book quote numbers are entered without being in order. ...
0
votes
1answer
1k views

vlookup and indirect and external shared workbooks

i have two excel workbooks that are both shared in our company folder. excel1 is where each employee has his own page to fill information and column 1 of each page is the row reference excel2 is a ...
0
votes
1answer
39 views

Focusing on particular information within a cell when doing a calculation

When performing a calculation using two cells in Excel, is there a way to focus on specific information within a cell, when entering the formula? For example, I have two time cells (start time and ...
2
votes
2answers
74 views

Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?

Suppose I have a spreadsheet with the following rows and columns: QTY MIN | QTY MAX | 1 Color | 2 Color | 3 Color | 4 Color 1 | 10 | $1.00 | $1.50 | $1.75 | $1.90 11 | 20 ...
0
votes
1answer
11k views

If cell B1=1 then DELETE cell A1 (excel 2003 formula) - How to do it?

I work with Excel 2003. If cell B1=1 then DELETE cell A1, and if cell B=0 then UNCHANGED. How can I do this? Example: http://oi33.tinypic.com/mwsm03.jpg Many thanks :)
0
votes
2answers
719 views

excel: how to leverage vlookup with count

I have some data in columns, several binary variables: | A | B | C | | 0 | 1 | 1 | | 1 | 0 | 1 | | 0 | 0 | 1 | | 0 | 1 | 0 | | 0 | 0 | 1 | | 1 | 1 | 0 | and then another variable with three values: ...
3
votes
2answers
7k views

How to implement MATCH and INDEX functions together in Excel VBA?

I am trying to find the row number of the first cell that is less than zero. So, by browsing through the net I was able to come up with this formula which works perfectly fine: ...
1
vote
1answer
92 views

Using an array formula to add up several cells in another worksheet (using INDIRECT)

I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but ...
1
vote
1answer
3k views

How do I add values in column B if column A contains the same employee surname more than once?

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once? I have been using what I ...
0
votes
1answer
155 views

Conditional Data Type based on Cell Value

I know that you can conditionally format the visual treatment of a cell using conditional formatting, but can I control the data type of a cell based on the value of another cell? I'm building an ...
0
votes
1answer
42 views

How to check for specific values in a column

I have a column that I'm working with that has a number of possible text options in it. The only two I care about are "Bad" or "Good." I'd like to write a function that results in 1 for "Good," -1 ...
5
votes
2answers
3k views

Highlight row if date is in current week

I'm working on a data table for finances. I want to highlight the row if the date is in the current week: What it should look like: The current condition formatting for the yellow fill spans ...
0
votes
1answer
1k views

Excel Time Subtraction

I think there is a way around the time subtraction issue I have in Excel. The problem is that when the time in cell F (for example 11:29am) is less than the one in cell A (for example 11:30am), the ...
0
votes
1answer
463 views

On Excel 2010, the =COUNTIF function displaying the value improperly

Well, here is the code I am using below: =IF(COUNTIF(G5:W13,"10"),"","$10") This code is working perfectly fine, however the number is not actually been treated as a number. I have done a number ...
0
votes
1answer
416 views

remove values less than a number in excel array

I have a value that needs to calculate stock based on the previous years sales: {=CEILING(SUM(IF('Inventory Transactions'!$H$2:$H$7137=LEFT($B6,3),IF('Inventory Transactions'!$O$2:$O$7137='Do Not ...
0
votes
1answer
2k views

Combine duplicates in Excel, sum some columns and weighted average the others

This question refers to an existing subjexct on our site. I need a variation on this solution. I have a spreadsheet with 8 data columns. The number of records can vary from a few hundred to about ...
1
vote
3answers
613 views

fill an ordered series in column A only if its corresponding cell in B having any text

I want that if in column B having text then in corresponding cell of column A number should be filled according to previous entry or so called series. I tried this for A1 =IF(B1=="","",1) but it is ...
0
votes
3answers
650 views

How to update cell references when setting the Formula property in VBA?

Here is a snippet of my code: For i = 1 To k If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then Else Range("D2").Offset(i, 0).Formula = Range("D2").Formula The ...
0
votes
1answer
2k views

Change formula font size in LibreOffice

I am trying to change the font size of the formula tool in LibreOffice. I have been told: To permanently change the default size (12 pt) used in LibreOffice Math, you must first set the size ...
2
votes
2answers
104 views

Excel macro checks if there is formula

I want to make a macro that checks if that cell get its value from a formula. I cann't find anything similar. Any tips??
0
votes
2answers
824 views

How can I easily combine multiple Excel columns to a single columns?

I have a dataset which has a seven possible conditions based on data in other columns. I had to split my formulae for those conditions across seven different columns. I would like to combine all seven ...
1
vote
2answers
741 views

Passing Range to ABS function in VBA

I would like to perform a function which looks like this Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))" Since my range keeps varying I would like to pass the range as a variable which looks ...
2
votes
1answer
3k views

Hide “inconsistent formula” error in a selection in Excel

This may seem easy to more advanced users, but I can't seem to find this out there. I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet. Unfortunately, ...
0
votes
2answers
64 views

Time Difference In Formatted Text

How can i find the time difference from like "9-10:30" formatted cell.What is the formula to find the "1.5" hour or "90" minutes from that cell.
0
votes
1answer
8k views

How to copy the value and format of a cell into another cell on Excel 2010? [duplicate]

I have been looking all over the internet for this question, but it has not been answered by anyone. I am, on Excel 2010, trying to copy (not copy and paste) the format AND value of a cell, onto ...
1
vote
1answer
87 views

Just get date stamp once in excel

To datestamp a cell in Excel I use the forumula : =NOW() The problem is that as I datestamp new cells all other cells which contain the formula NOW() are updated with the new datestamp. Is there a ...
0
votes
2answers
975 views

How to count number of rows excluding rows with duplicate data

I have a table in which the column "Doc. No." has duplicate info as below; please advise on which formula I should use to count the number of unique Doc. that I have (as reference to the below table ...
-1
votes
3answers
1k views

How to lock a formula with self reference?

I'm trying this: C9 formula: =-40+C9 What I want to is being able to enter some value in C9 that will not overwrite the formula and get a result in that cell. For example: If I enter the value 140 ...
-1
votes
2answers
356 views

How can I refer to a cell that is a constant number of cells away from a particular cell?

I have two identical tables on a sheet: one that contains text for reference, and a second that contains values. I am trying to sum up some cells in the second table but using the addresses of ...
-1
votes
1answer
81 views

Copy text from “joined cell” to another sheet's smaller joined cell

Normally, text appears in the cell when it contains a reference to another (=Sheet1!A1 will show the contents of sheet A1) However, if both target and destination are joined cells, and the target is ...
2
votes
0answers
239 views

VBA - Arrayformula to a range stored as array variable

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example: Selection.Offset(2, 0).FormulaArray = ...
2
votes
1answer
955 views

Find last row in sorted table for matched value

I have a table in Excel, which for simplicity sake we will define as: Order# | Date | Price | 123 | 1-1-2013 | 100 | 135 | 1-2-2013 | 150 | 145 | 2-4-2013 | 100 | 145 | 2-5-2013 ...
2
votes
3answers
340 views

Counting frequency of comma-delimited tags in Excel

I have an excel spreadsheet with a list of seminars and classes that students have taken that (when abridged) looks like: (A)___ClassName________|(B)_________Tags____________| Astrobiology ...
0
votes
1answer
111 views

VLOOKUP in Excel with IF statements?

I'm not sure whether IF statements are actually needed in this problem; however, I'm totally stumped. In the first worksheet I have a column for "years employed" (Column D6:D107) ranging from 0.1 to ...
0
votes
5answers
242 views

Excel - Sum of values in a column given the order type available in another column

I have excel 2010 and I am trying to do the following (the table below has been done manually) Customer Order type Revenue Sum 001 Pears 50 ...
0
votes
2answers
434 views

Auto orderlist Excel 2010

I'm using Excel 2010 to create a inventory of my shop. Now I would like it to automatically create a order list in the second sheet. My first sheet look like this. Item: Stock: Min. Max. ...
1
vote
1answer
137 views

Excel formula creation

I have a spreadsheet with a lot of job related data. One column has names of people in it and another column has the dates on which those people completed jobs. Each person may do more than 1 job per ...
0
votes
2answers
618 views

Copy multiple cells from one to multiple worksheets

I have a workbook in Excel 2007 with 52 worksheet each representing a week of the year, each worksheet is laid out the same with 16 rows and 26 columns to give me 14 personnel and 24 hours with other ...
0
votes
1answer
547 views

How can I get the hex value of an 8 row column with depending on background color?

I have a spreadsheet that I'm using to help visualize a character bit map for each ASCII character. For a 5x7 font, I use 5 cells wide (columns) and 7 cells tall (rows). I then marked the background ...