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

learn more… | top users | synonyms (3)

1
vote
1answer
17 views

VBA + Polymorphism: Override worksheet functions from 3rd party

my company makes extensive use of a data provider using a (closed source) VBA plugin. In principal, every query follows follows a certain structure: Fill one cell with a formula, where arguments to ...
0
votes
0answers
8 views

How to get cells to default to zero or calculate additonal fees, based on selection from a drop-down list

I am building a Pay Rate Calculator worksheet with a Flat/Base pay rate & numerous Overtime pay rates. I would like to be able to have the "Overtime" pay rate cells to change depending on my ...
0
votes
0answers
19 views

Auto Updating Month Over Month and Year Over Year formulas

I have a spreadsheet that has several different sheets (Visitors, Buyers, Conversion Rate, GMS). Each sheet has the data on that statistic monthly from 2013 onward. So sheet 1 shows monthly visitors ...
0
votes
1answer
27 views

Excel: How to extract group of the same rows and their values into columns?

How can I extract the second table from the first one? I need to extract those titles that have exactly three costs (in the following example: f1 and f3). I tried using pivot tables but could not ...
0
votes
0answers
8 views

Populating table using Indirect function and copying formula across the whole table

Hi I am currently creating a budget and am stuck on the formulas i need to get the data on various worksheets pulled into specific sheets. I have created worksheets for Jan to Dec for my household ...
0
votes
1answer
20 views

Excel: how to link cell from one sheet to another

could you please, help me? i would like to find out how to do this: i have a sheet 1: with columns: number, name, color. i would like on sheet 2 to have 3 cells, where after entering the number, two ...
0
votes
1answer
17 views

How can I use COUNTIF and TODAY functions to identify work deadlines in multiple worksheets?

I've been playing with our spreadsheets at work and have been able to create a basic formula that will identify when there is a deadline on today's date that needs completing. The issue I now have ...
0
votes
1answer
21 views

How do you add text to the end of a cell in Excel

I am wanting to add the same text to the end of about 10,000 cells, but I don't want to go through and manually type it in on every cell. So basically, I want to: A1 A5 text =A1+"file" ...
1
vote
1answer
38 views

Excel One cell to many formula (I realize this may not be possible)

First of all I want to start by saying I know this is either extremely difficult or impossible. I have data (from wikipedia, on any airport in the airlines and destinations list), in one column is ...
0
votes
1answer
17 views

Use an user defined function for data validation

I created a named range which is the result of a user definer function. Let say my rage is named List and has three items, if I select three cells and enter =List and press Ctrl+Shift+Enter, I get ...
0
votes
0answers
12 views

How to check if cell contains entry from another column?

So basically I have a column(A) with company names and their adress together. I have another sheet with a column(B) with the company names that are members. I wanna check if the companies in A are ...
0
votes
2answers
26 views

Excel: Random select within table that meets two criteria

Here is an example of my data: Name: Office: Years: John Smith Canada 1 Bob Smith Canada 1 Jake Smith Canada 1 Sarah Smith Canada 1 On another sheet I want ...
0
votes
1answer
19 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
24 views

Get result of formula as text

I have an Excel doc which populates a matrix using formula, for example; | A | B | C 1|=1+2 |=2+2 | 2| | |=4-1 would display as; | A | B | C 1|3 |4 | 2| ...
1
vote
1answer
19 views

How to return the text left of line break

I have a Excel sheet, which contains a cell with this content: Text1: "abc" linebreak1 .... Text31: "mno" linebreak31 Text 32: ... linebreak32 ... Text50: "xyz" linebreak50 No I will ...
0
votes
1answer
23 views

Conditional formatting

What I am looking to do is have a conditional format set up so that if row G contains the value "In progress" "With 3rd Party" or "Identifying" and row H, J or K contain no value then highlight rows A ...
0
votes
2answers
13 views

IF Function on excel - i need to put a formula into one cell using to < < however it isnt working

I need to put in one cell that IF the value of H3 and F3 is lower that 25000 but higher than 0 it will equal 29.95, if false 0 the formula =if(0
0
votes
0answers
10 views

LibreOffice calc countifs with date

I got 3 columns. 1 column(D) text, 1 column(T) 0 or 1, 1 column date(A) 01.01.2010 till 31.12.2014 . I want to count up until a specific date, for example 10.10.2011: ...
0
votes
2answers
28 views

Excel: Percentages and ranges

I am attempting to make a bonus spreadsheet for work and am having some issues with the following. I am using this formula to determine 80, 90 and 100% ...
-1
votes
0answers
10 views

Excel 2010 SlopeA Function [on hold]

Addin or create a SLOPEA function that works like the AVERAGEA function treating text cells as a 0 value. I need either a link to getting the addin or the VBA code for creating the function.
0
votes
1answer
54 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 ...
-2
votes
0answers
22 views

Excel formula to copy text from another sheet in other cels

Hello I have this situation. In sheet 1 I have rows a1, b1, c1, d1 and pasting them to sheet2 (sh1)a1 into e8 then (sh1)b1 into e10 then (sh1)c1 to e12 and (sh1)d1 to d22. Then from sheet2 I wont to ...
0
votes
2answers
37 views

How to sort columns in Excel so that only one value appears in each row?

Say I have a a spreadsheet with the following data: A B C D --------------------- 2 1 0 2 3 2 1 4 5 3 3 6 7 5 6 8 11 8 10 10 ...
1
vote
1answer
16 views

excel grid to columns

I have data in an array/grid like this: Name | Group | A1 | A2 | A3 name1 | Group1 | 14.95 | 19.95 | 0.00 name2 | Group2 | 4.95 | 9.95 | 12.95 name3 | Group2 | 49.95 | 0.00 | 0.00 And I need a way ...
2
votes
1answer
30 views

Two types of formulas in one cell - Excel 2013

Thank you for your time. I am using Excel formulas for the first time ever and I am stuck (I am sure that I am not using the proper terminology to search and thus cannot find the help I need). It is ...
0
votes
1answer
21 views

Construction Quote Log

I'm reworking my company's log of the proposal's we send out. Trying to indicate the total dollars that we could conceivably be awarded. The challenge I'm having goes something like this: We are a ...
0
votes
0answers
9 views

Trouble with IRR formula in Excel 2008 on MacBook

I am getting an error message for a relatively simple calculation. I am trying to calculate the IRR over a 2.5 year period for a set of data in which -11,000,000 is the initial investment and zero ...
1
vote
1answer
17 views

Using column data from another worksheet

I have a worksheet with one column and that has a list of my company names and what I want to do is be able to create a stats worksheet where I can use those company names without changing both ...
0
votes
1answer
23 views

Copying VLOOKUP formula in multiple sheets, where sheet number automatically changes

I have created a progress card in Excel (there are 50 such sheets, one each for 50 students). There is one tabulation sheet also with the names of the 50 students. So, when numeric marks are entered ...
0
votes
0answers
23 views

Go to first non-negative cell in a row and divide by another cell

I'm looking to have Excel calculate the financial payback period for me. In words, I'm trying to count and return the first non-negative number in a row and divide that value by a corresponding cell. ...
1
vote
1answer
29 views

How to perform a function based on surrounding cells in Excel?

My rows on my spreadsheet look like the following: foo | cherry | 400 bar | lemon | 300 foo | cherry | 500 bar | cherry | 70 I want another row to make calculations in column C based on the ...
1
vote
0answers
15 views

Excel Table function

I got a Excel file. There is a formula, {=TABLE(C7,C5)}. I cannot find any table name from ribbon Name Manager, so I think there is no table. Then what is this formula doing ?
0
votes
0answers
29 views

Excel 2010 Compile Data from Multiple Worksheets into Summary Spreadsheet

I've compile a structural steel and architectural finishes material take off for an industrial building. I simply tallied the quantity of each length of each shape on a sheet corresponding to each ...
1
vote
1answer
34 views

Working with cell addresses in excel with VBA

I have a Excel sheet in which are multiple forms. The forms look all the same and they have exact the same cells between them. They are ordered in 4 x 3: x x x x x x x x x x x x x x x x .... There ...
-1
votes
0answers
37 views

How do I use VLOOKUP or other lookup function to return all results of criteria?

I have a spreadsheet like this: **Column A:** **Column B:** apple 1 apple 1/red apple 1 apple 1/green apple 1 apple 1/yellow apple 2 apple 2/blue ...
0
votes
1answer
23 views

excel text to specific cell

so i have this problem i want to get a specific cell from text i mean that i have made a table 1,2,3... A,B,C,.... i made a vlookup to search for specific letter in number so if i press 1 in a A3 it ...
-1
votes
1answer
25 views

Excel, Write a value based on a counter

What I want to achieve is very simple. Based on the value of an Excel cell (let's consider it X), I want to write a specific value in X subsequent cells. For example, if A1 has the number 3, a ...
0
votes
1answer
10 views

In OpenOffice Writer, how to populate a column of a table with a sequential increase in dates?

I am using OpenOffice Writer. In it I have a table for a time sheet. The first column is for the dates which begin from eg. 01/01/2014 and should end in eg. 20/07/2014. These dates are not already ...
0
votes
1answer
23 views

how to create a YES NO notification for time?

I have a formula that shows hours and minutes of how long it took to notify a client. I need to create another cell to say YES if that notification is 4 hours or longer. I currently have the formula ...
1
vote
3answers
36 views

Excel date & time formula-round up cell value

I'm preparing a report of usage statistics for a group study room reservation system we use at the library where I work. I've exported all the reservations from MySQL in Excel for the past year and am ...
1
vote
2answers
34 views

Excel to calculate summation with exclusion list

Say I have a table like below: Task Time a 1 b 2 c 4 d 8 -------- sum 15 When a task is finished, I would like to add strike line to the row and exclude it from summation. Adding ...
0
votes
1answer
20 views

Calculating frequency of only visible cells

I'm having trouble calculating the frequency (in order to plot a histogram) from filtered data. As for now, on Sheet1 I have the data of ten temperatures with a filter, I assigned a name for each ...
0
votes
2answers
37 views

Create New Spreadsheet of data from many Excel workbooks

In retrospect, this is an issue of data-collection, but I want to see if there is an easy way to do it without doing each file. I have about 350 separate Excel workbooks. Each workbook has a bit of ...
0
votes
0answers
33 views

Matching a Date Conditional on its Distance to Another Date

I have a set of names and datetimes on two sheets. One sheet we'll call LargerSheet and the other we'll call Sheet1. On LargerSheet each name has multiple datetimes associated with it. On Sheet1 ...
0
votes
1answer
30 views

Excel Formula Assist - Find missing numbers

I have a spreadsheet exported from my database that contains Roles and Account IDs. There are 4-5 role names per account ID. I need a formula to display any accounts that do not have a role of RBD. ...
0
votes
1answer
22 views

How can I use a formula or conditional formatting to format mutilple criteria?

I am trying to do an excel function, either an IF formula or use conditional formatting to do an AND formula. I need it to produce all three results in one formula, it is: if the date is before ...
1
vote
1answer
24 views

Sumproduct, Sumif, Indirect and named ranges issue

I have this formula that works: =SUMPRODUCT(SUMIF(INDIRECT(Financials&"!$B3:$B10000"),'Information PG1'!$K$2,INDIRECT(Financials&"!d3:d10000"))) where Financials is a named range. If I ...
0
votes
2answers
45 views

Indexing a Date Range if Date is Greater Than and Within n Days of Another Date

In a previous post I asked a question about how to find the most recent date associated with a particular entity and have that value returned in Sheet2 so long as it was greater than the associated ...
0
votes
1answer
29 views

Search from bottom with multiple criteria

I am trying to search a data table in excel and find the bottom most row that meets a specific criteria. I thought I could use this LOOKUP trick but it only returns a #DIV/0 error. This is what I ...
4
votes
4answers
190 views

Showing whether a list in Excel has a 1 or a 0 in it

So I have a column in excel that I want to roll up into one row. Essentially the column has a 0 if a feature isn't present, and a 1 if it is. What I want to do is get excel to check a column, lets ...