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

learn more… | top users | synonyms (5)

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