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

**0**

votes

**1**answer

575 views

### Excel SUM or COUNT based on related fields from two Tables

I have two tables in Excel
The small table with 50 rows...tblGroups[#Headers]= Employee ID; Name; Group.
The second table with 3000+ rows...tblData[#Headers]= EventID; Date; Employee ID; ...

**0**

votes

**1**answer

230 views

### Combine Excel worksheets despite adding new sheets

I have an Excel worksheet that works as template for a questionnaire. My aim was simply to copy the worksheet into a new one while preserving the formatting and other stuff, and I could then just fill ...

**-1**

votes

**2**answers

107 views

### Calculate 9 to 5 hrs

I want to calculate number of business hours between 2 days.
For eg.
A B
15/05/2013 13:25 31/05/2013 10:20
19/05/2013 09:12 22/05/2013 21:10
I want to get result in C ...

**1**

vote

**2**answers

5k views

### How to do a “great circle” calculation in MS Excel or LibreOffice?

I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called ...

**-1**

votes

**1**answer

23 views

### Excel 2010 how to label a row

Coumn A in my worksheet has a list of books : Tom Sawyer, Jack and Jill , Phantom, Phantom in four rows. I want to label each row in column B using the logic IF more than one copy of a book is sold it ...

**0**

votes

**1**answer

2k views

### How to return data if a value falls within a range

I am trying to make a formula to calculate certain costs associated with a property value, based on its value:
Value of Property Rate to apply Deduction
Up to €92,407 0% ...

**0**

votes

**0**answers

31 views

### I need help writing a macro that will check a condition, and then paste a value into a different cell [duplicate]

I want to have a macro that will check to see if a specific value exists in a data range, and if that value does exist in that data range, then I want to past a line of text in a different cell. Could ...

**0**

votes

**2**answers

46k views

### How do you write an Excel formula that will paste a specific value in a different cell?

So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the ...

**1**

vote

**4**answers

2k views

### How to calculate age in years?

In Excel 2010, I'm using the formula =(TODAY() - 05/31/1996) / 365.25 to calculate the age in years between today's date, May 30, 2013, and May 31, 1996. I'm expecting some number close to 17.00, but ...

**2**

votes

**1**answer

573 views

### Excel Conditional Formatting cell's value that has number suffix, like 99th, 23rd, or 1st

So I am filling a cell with Blue, Green, Yellow, or Red based on a Percentile Rank that is returned, that is numbers like ( >=90th=Blue, >=60th=Green, >=30th=Yellow, <30th=Red )
Currently ...

**27**

votes

**7**answers

228k views

### In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.
I've named the range A3:A10 'some_names', and I'd like ...

**2**

votes

**2**answers

747 views

### SUMIF by category and date in range

I have a table where I keep a transaction log of expenditures as so (excerpt)
Date Paid Purpose
where the date is the date when the transaction is conducted, Paid is the actual amount paid ...

**1**

vote

**1**answer

3k views

### Tab (\t) is not recognized as columns delimiter when pasting into Excel 2013

I'm pasting data separated by tab, usually the excel would be a "little" cleaver and separate the data by columns at each tab character.
I don't know why, but Excel doesn't recognize the tabs anymore ...

**0**

votes

**1**answer

519 views

### adding 'weight' to a value

I work in customer service center where we monitor calls and grade them.
We have an excel sheet where we score these calls, and my question is, we want to give a greater weight fro 10 points an agent ...

**2**

votes

**5**answers

1k views

### Does excel have an arrow key shortcut to skip blank cells?

In most versions of Excel (I'm using 2010), pressing ctrl+up or ctrl+down will take you to the edge of the current data region or the start of the next data region in a worksheet. Effectively skipping ...

**3**

votes

**1**answer

4k views

### SUMPRODUCT with condition

I have 2 columns B="Points" and C="Grade"
And i'm trying to find the formula to sum the products of all points*grade for grades >= 60.
I tried this formula with many others, with no success:
...

**3**

votes

**2**answers

792 views

### Get first and last value from range in a spreadsheet using other column's data for filter criteria

I have data in my spreadsheet in a worksheet like this:
Where the data goes in this format
[ID] | [Date] | [Hour] |
And I have another worksheet where I want the data to be like this:
Where I ...

**2**

votes

**4**answers

4k views

### How can I use drag-down to copy values horizontally?

I am dealing with one table full of digits and I want to use drag-down function to take digits horizontally every two or three columns if there is digits in the cell. Here is part of my table:
A ...

**3**

votes

**4**answers

20k views

### How to add/subtract months from given date in Excel

Here is my scenario:
I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...

**1**

vote

**1**answer

292 views

### How do I create a list of phone numbers for people whose reference numbers appear in two columns?

I have two sets of data in columns A & B and also in columns C & D. Some of the content in column C & D does not appear in columns A & B:
A = reference numbers
00001, 00002, 00003, ...

**0**

votes

**1**answer

846 views

### Excel: URL with normal Text in a Cell

How can you add an HTML URL to a cell with normal text?
I have a cell with normal text, but want to add a URL, but I don't want the whole cell to be the URL.
i.e.
Please go to this link: ...

**1**

vote

**1**answer

192 views

### Refering certain rows in other sheets based on a particular condition

I require help for making the following table: Consider that I have three sheets in an Excel workbook.
The first sheet is the summary page. The next two are Subject1 and Subject2.
Each of these ...

**1**

vote

**1**answer

61 views

### How to count based upon multiple conditions

I am trying to create the following formula
=SUM(IF(AND(K3:K138="Tekken",L3:L138="American Football"),1))
However, it always seems to result in zero, even where a single row would have both values ...

**0**

votes

**1**answer

336 views

### Excel: Using MATCH as row argument of OFFSET

Cell F10 is a choice-box: 'Best', 'Base', 'Worst' case. I'm trying to change Revenue growth (F11) based on that selection.
How do I use match as the row argument of the OFFSET function in cell F11 to ...

**0**

votes

**2**answers

608 views

### VLOOKUP to search multiple values in a Cell

I have a VLOOKUP that looks up an Account # and Returns a Rep. The Account # field contains historical data (every account including inactive ones). How can I setup a VLOOKUP to take to see pull the ...

**0**

votes

**2**answers

96 views

### Need simple macro for “match” formula

Are column B cells hidden somewhere anywhere in column C? Give results in column A. (true/false or yes/no)
I'm unable to post the simple Excel image I've created as I'm a guest, but it shows simply ...

**-1**

votes

**1**answer

555 views

### Is it possible to build a cumulative formula that will sum on new worksheet added daily?

I have a worksheet that the user opens each day and clicks a button that copies the current sheet to a new sheet and renames (Day 1, Day 2, etc.).
I want to keep a cumulative count in some of the ...

**0**

votes

**3**answers

1k views

### How do I separate three different phone numbers concatenated in one cell into three new rows?

I am doing work for a client and need to know how to separate these phone numbers.
I have
0121 396 0501 / 0121 123 434* / 0797 978 8265
in one cell and I want to end up with the same row ...

**0**

votes

**3**answers

115 views

### Nested IF statement

I'm having a problem with this code for nested =IF statement. The range is basically:
<=16 to get 5 pts
16-17 to get 4 pts
17-18 to get 3 pts
18-19 to get 2 pts
above 19 to get 1 pt
and my code ...

**0**

votes

**3**answers

12 views

### How to get displayed decimal place

How to return the displayed decimal place only in excel by formula? for example, A1=9.123, it formated to display 9.12; how to get the decimal number of 2 in this case;

**0**

votes

**1**answer

8k views

### Compare two date columns and highlight the newest

I have two date columns in the same sheet. Column B receives its data from a vlookup function.
COLUMN A | COLUMN B
2013-07-05 | 2012-01-01
What I want to do, is to compare the two date columns ...

**0**

votes

**3**answers

282 views

### how do I average the lowest 3 of the final 6 numbers in a row of 22? Some cells may be blank

How do I automatically average the lowest 3 of the last 6 numbers in a row of 22 that adds one number per week? Obviously I need at least 7 numbers to begin using this formula. Also, some cells in ...

**0**

votes

**1**answer

617 views

### Excel 2010 Formula Bar - autofit?

In Excel 2003, the formula bar would autofit the height of the active cell. In Excel 2010, this doesn't happen. Is there a way to revert the behavior to the Excel 2003 functionality without trying ...

**1**

vote

**1**answer

2k views

### LINEST function in excel vba

I am trying to figure out the 2 order equation (Y = ax^2 + bx + c) for a set of data values. When I use the inbuilt function in excel
=LINEST(C2533:C2537,B2533:B2537^{1,2})
I get the following ...

**-1**

votes

**1**answer

164 views

### How to check if a date is in the future

I want to make a spreadsheet reporting daily and weekly goals for a certain quantity and their achievement, one reporting sheet per calendar week. Imagine that I open it on Wednesday, then I want to ...

**0**

votes

**1**answer

312 views

### How to mix Latin and Greek script in Libreoffice formulae(Math)?

I need to write a formula which uses both Latin and Greek scripts.
So far I can only make it so that either all characters are Latin, or they're all Greek.

**1**

vote

**2**answers

59 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

**5**answers

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

**1**answer

327 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

**2**answers

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

**2**answers

357 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

**2**answers

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

**3**answers

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

**1**answer

274 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

**1**answer

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

**6**

votes

**3**answers

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

**1**answer

237 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

**1**answer

75 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

**1**answer

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

**1**answer

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