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

learn more… | top users | synonyms (5)

0
votes
1answer
56 views

Convert a range to a percentage in Excel

I have a range of figures I want to convert to percentage. For example I have temperature readings taken every hour over a full day. The range is from 4.5deg to 15.5deg. I want 4.5deg to equal 0% and ...
0
votes
2answers
72 views

How to calculate with Microsoft Excel the correct amount of minutes?

64 people receive services 105 minutes per day. How can i use Excel to calculate the total minutes of service per day and then multiply by 7, so i have the total amount of minutes per week, and then ...
0
votes
1answer
63 views

Can't get VLOOKUP working with roster spreadsheet

I have an Excel spreadsheet set up as a roster. The way the roster is setup is for the ease of the person creating the rotation. This roster is not easy to read for the staff. Mon ...
0
votes
1answer
57 views

Excel Values Require Enter within Cell to recognize as number

I have a worksheet where I am getting direct output from STATA that has a weird character format that included " =[ number ]". I put in a new column to strip the characters and leave the numbers. ...
0
votes
1answer
107 views

Make formula impossible to find / read

I have developed a spreadsheet for performing various calculations which our employees will complete and then distribute to clients / competitors. To protect the "intellectual property" of the ...
0
votes
2answers
61 views

Converting comma seperated values in multiple rows

I am in urgent need of importing huge data from excel to SQL. one of the excel sheet contains data as follows. ID Name Category Section 1 Books Stationery KG1,KG2,1 2 Pencils ...
0
votes
1answer
33 views

How to update range of columns in excel

I have an excel sheet where from one sheets's Id used as reference in next sheet. The Id's are sequential. Like this : Sheet 1 Sheet 2 ID Name ID Sheet_1_ID Text 1 ...
0
votes
1answer
48 views

Is there a formula I can use to count the number of occurrances of a name in 1,2,3 order?

I have an excel spreadsheet containing a list of names and "scores" that they have received for a specific date. Each name should have 3 scores each month and I'm looking for a formula that can help ...
0
votes
3answers
1k views

Excel formula to compare single value in one cell with multiple values in other cell

I have a value in Column A, which I want to compare with multiple values in column B, and depending on that value, put the answer in column C. For example, using the table below, it searching in ...
0
votes
3answers
100 views

Average Difference and Direction Between Values in Excel with Blanks

I have a sheet that looks something like this: Sheet 1 1 2 3 4 5 6 7 8 9 10 11 1 6 2 3 5 3 ...
0
votes
1answer
83 views

Generating Joint Density Function in Excel

Is it possible to have Excel calculate a joint density function based on two sets of descriptive statistics generated by the Data Analysis tool? To explain further, suppose I have two means a and b ...
0
votes
1answer
365 views

Excel Formula: An Array formula containing VLOOKUP, SMALL, IF, OR

I am trying to create a formula that considers the data from three columns Y, H and V on my tab titled 'Data', returning the smallest value in A that meets that condition, once found Vlookup that ...
0
votes
2answers
48 views

Getting current value and passing it to another function

A B 1 Date Data 2 1/1/2014 1 3 1/2/2014 2 4 3/5/2014 3 I wanted to sum up rows for certain months. With month I can get the month and sumif ...
0
votes
2answers
93 views

Searching and Inserting Data Based on Date Range

In another post, a formula was given that was meant to help search and insert an entry if the date associated with the entry was less than the date on another sheet. The formula was as follows: =IF( ...
0
votes
2answers
36 views

Excel - Is there a function to return a number based on which string is in a cell, like CHOOSE but not?

I need a function =Func(A1, "Alex, "Betty", "Charlie") that returns the index of the matched entry. If this function is put into B1:B13, it would show "Charlie" 3 "Alex" 1 "Alex" 1 "Alex" ...
0
votes
1answer
88 views

Excel help to lock formulas from adjust on cell insert

I have a excel workbook where there is a hidden sheet that basically pulls data from other sheets in the same workbook just using the = formula. The problem I am having is if I add a column or cells ...
0
votes
1answer
27 views

Automatically Copy the only Credit rows into a Sheet

I am trying to use a formula by the below, With out use Developer Options. I have a excel workbook and there is a two sheets One is - One sheet is (sheet1)- TXN. ID Transection Type After ...
0
votes
3answers
367 views

Excel filter duplicate values in column A and filter out rows which have column C blank

How to filter duplicate values in column A and filter out rows which have column C blank. I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next 800 rows are ...
0
votes
1answer
97 views

Excel Formula Challenge

I have a fairly specific Excel problem and I am hoping someone is savvy enough to come up with a working formula for me. I am using Excel 2011 for Mac btw. So, I have a list of about 134 ISBNS, for ...
0
votes
2answers
45 views

Showing name of row instead of excel cell name

I am having extremely long formulas over an extremely big sheet. At the moment I am tracking the formulas with the Formula Auditing Tool. However, my idea would be to just replace for example in a ...
0
votes
1answer
432 views

Excel Help: Search Column A, Sheet 1 for same value in Column A, Sheet 2, return value of Column B, same row in Sheet 2 [closed]

I'm surprised I couldn't find the answer on Google, maybe I am being silly. I want to perform this function:- I have two sheets in Excel, lets say they are Sheet 1 and Sheet 2 In Column A, Sheet 1 ...
0
votes
1answer
548 views

Excel - minifs function returning 0

I'm trying to write a function in Excel 2013 that returns the minimum of a set of cells that meets two criteria, but the function always returns zero. On one worksheet, I have a table with several ...
0
votes
2answers
77 views

Calculating time between 5a-1p or 530a-130p

I was wondering if it is possible for Excel to calculate time span if each time block is in 5a-1p format in one cell. That way I could add the hours for each week without changing too much. Sample ...
0
votes
1answer
285 views

Excel SUM From Different Sheets IF Date Found

I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on ...
0
votes
3answers
52 views

Excel Extending Equations

So I have an excel table that is multiply 1 value against several other values. It looks like this: So I want the equations inside cells C14 to F14 to be B14*C5, B14*C6, B14*C7, B14*C8 respectively. ...
0
votes
1answer
129 views

SUMIF with multiple match criteria in Excel

I'm trying to create a formula so that if in cell E2 it says "Market", "Limit", or "Stop" it adds the numbers in cells L2 and M2. =SUMIF(E2, "Market" "Limit" "Stop", L2+M2) These expressions are ...
0
votes
5answers
1k views

Excel COUNTIF Slow

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...
0
votes
1answer
61 views

Excel 2010 Countif Formula

I'm trying to make a spreadsheet to display stock orders. I am including both executed and unexecuted orders, but I'm trying to display how many orders did or did not execute using Countif. My orders ...
0
votes
1answer
75 views

Matching grades to names in separate sheets

I'm having some trouble with an excel formula. What I have in sheet A is a list of student names in (Fname LName) format, and then I have the same list of students in a separate sheet along with exam ...
0
votes
2answers
79 views

Creating a formula that queries data in Column B based on a selection from Column A in Microsoft Excel

I am reasonably familiar with Excel formulas but I need to construct a more complex query and am not having much success at the moment. I have simplified what I am trying to achieve for the purpose ...
0
votes
2answers
143 views

Excel if/then formula

I am wondering if there is an if/then formula for the following situation. I am trying to populate a monthly calendar. I want the calendar to show the value in A1 if the value in B1:B100 is equal to a ...
0
votes
1answer
267 views

Defined Name to give text or date value instead of numeric value

I had been trying out Define Name in Excel. I defined a name for a cell in which I would put a text or date value or I may leave it blank. But when I use this defined name in another cell, the second ...
0
votes
1answer
56 views

Nesting the IF Function in Excel when there may be #Value! errors in the target cell

I'm trying to get some help to change a formula: =IF(S4>8000,3,IF(S4>6000,2,IF(S4>4000,1,IF(S4<4000,0)))) So that it returns a value of 1.5 if the target cell contains an error value. ...
0
votes
1answer
99 views

Can't compare a cell to a column in excel

I'm trying to compare a column to a cell in excel several times. The columns are copy pasted from a PDF (only way to get the information). The results are bad, with trailing spaces, some completely ...
0
votes
1answer
114 views

Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column. When a match is found I want to get the corresponding ...
0
votes
2answers
79 views

Indirect turns up with #ref error

I want to use INDIRECT in a formula like =INDIRECT(CONCATENATE(A1,"+",A2),0); where A1 = 7 and A2 = 8. It comes out with #REF error all the time, but if I use the INDIRECT formula like this: ...
0
votes
2answers
77 views

Define a range based on the result of the INDEX function

In Excel it is possible to write a function looking like that: =SUM(A1:INDEX(...)) Where the end of the range of the data to sum is dynamically defined by the result of the INDEX function. The ...
0
votes
1answer
502 views

Generate ordered numeric list in Excel based on cell value

I'm not sure if this is possible, but I would like to create an ordered numeric list automatically based on a cell value. For example, if I have a cell with a value of 100, I want excel to ...
0
votes
2answers
61 views

Excel 2007 - refering value from other worksheet in another function in excel

I have two worksheets. I have data in Sheet2 in column A for which i want to use average function. In sheet1 i have values in only two cells. B1 cell has value 5 and B2 cell has value 15. Using these ...
0
votes
1answer
542 views

How to do a sliding window in an excel table?

I have a table with a date column and a value column like this |A |B 1 |date |value 2 |2013-12-10 |1 3 |2013-12-10 |3 4 |2013-12-09 |2 5 |2013-12-04 |5 6 |2013-12-02 |2 Dates can ...
0
votes
2answers
1k views

VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg. I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...
0
votes
2answers
53 views

IF function only working if Value is TRUE and remitting a #NA if the Value is FALSE on Excel

Can anyone help me with the following formula? Basically it works when the condition is TRUE but gives me an error when the condition is FALSE. I would like the cell to remit the value "0" if the ...
0
votes
1answer
1k views

Excel 2007 adding a percentage increase

Value = 20 is a constant number Percentage increase to "Value" in levels of 2%, 4%, 5% up to 20% I would like to enter in a cell the LEVEL of the object and it return the VALUE + the percentage ...
0
votes
2answers
2k views

How to extrapolate or predict a project completion date

I have a few data points consisting of a percentage completed and a date. I would like to be able to predict the expected completion date based on the progress made so far. If this cannot simply be ...
0
votes
2answers
123 views

How do I determine if a value is between range of values specified in two columns and return another value from the row where condition is true?

I have a value that I enter in cell B5. I need a value returned from column C and the corresponding row where the value is between minimum values in A10:A12 and maximum values in B10:B12. In the ...
0
votes
2answers
317 views

Multiple conditions in if statement on non-contiguous cells in Excel

I would like to perform an if statement with conditions on the values of multiple non-contiguous cells, something like ...
0
votes
1answer
1k views

Excel summing named range

I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to ...
0
votes
1answer
1k views

Subscripts inside Excel formulae

I have a formula in Excel that evaluates the l_x's (number of lives aged x) in mortality data: =IF(J3>J2,"l_x increased",IF(J3/J2-1>=devianceThreshold,"Deviance too high","")) Accross the ...
0
votes
2answers
4k views

Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula. Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...
0
votes
1answer
508 views

Find and Replace VBA/Macro in Excel 2010

Another Excel VBA question. I'm using Excel 2010 I want to create a Macro that will remove the following 3 symbols in any cell for a selected range: [ ] " For example; If I select a range of ...