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

**0**

votes

**1**answer

21 views

### Excel - How can I collapse data, eliminating blank rows?

I have a 3 column, 20 row table. (A1:C20)
The 3 columns are each 1 digit of a 3 digit number.
Some of the rows have 3 digits, some are blank.
I need a way to return all the non-blank rows of 3 ...

**0**

votes

**1**answer

18 views

### Formula to return values into a number of cells based on another cell's value.

If I have a cell (A1) value is "2" (set value)
& another cell (A2) value is "4" (number of cells in 1 column i want the set value to appear in)
How do I return the values in B1:B4 to all be "2"?

**2**

votes

**2**answers

17 views

### Moving Right When Dragging Formula Down

I have a SUMIF formula as follows: =SUMIF($B$2:$B$5758,">1",N$2:N$5758)
Is it possible to make it so that when this formula is copied down the formula becomes =SUMIF($C$2:$C$5758,">1",N2:N5758) ...

**0**

votes

**0**answers

11 views

### Excel VBA - User Defined Function to turn string into formula

I'm looking for something similar to the solution posted here:
Excel function that evaluates a string as if it were a formula?
Problem is this does not seem to work with a function in there.
This ...

**0**

votes

**1**answer

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

**1**

vote

**2**answers

27 views

### Excel TRUE/FALSE IF formula

I have a "Pass" column (A) and a "Fail" column (B) in one worksheet. I have a column in another worksheet where I need the following to happen:
If A is true and B is false, return "Pass"
If B is ...

**0**

votes

**0**answers

20 views

### excel - ask for input to be used as new cell values

I use excel 2007 and I want to create the following function:
At first I need to add a vba function button that prompts the user to add four different values, e.g. a name, a number, a date and an ...

**0**

votes

**0**answers

3 views

### Google Sheets Suffix To Column Cells [migrated]

I'm editing a spreadsheet of product data that has a category column. I'd like to create a new "Left Handed" as well as "Right Handed" category in addition to each products current categories.
I've ...

**0**

votes

**1**answer

15 views

### Excel: INDEX and array on another sheet

My raw data is a list of students and their grades on Sheet 1.
6th Bobby
6th Carl
7th Tommy
8th Bobby
I want Sheet 2 to give me a list of students based on the grade I input into cell A1.
example: ...

**0**

votes

**1**answer

17 views

### Calculate Break Point Weight

I have a table like so:
Miles Weight Rate
50 1710 54
50 3417 44
50 7253 38
50 12025 36
50 16052 35
100 1716 55
100 3429 45
100 7269 39
100 12027 37
100 16055 36
Where ...

**0**

votes

**2**answers

32 views

### Conditional Matching Based on Datetime

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

**0**answers

37 views

### Sum of Multiple VLOOKUPs

I am trying to find a formula that would help me sum the results of multiple VLOOKUPs. For example, you have two sheets that look like the following:
Sheet1
Fruit______ Orange_Apple_Banana
...

**0**

votes

**1**answer

18 views

### Excel 2010 Formula: Finding a dynamic column based on string value, then count # of “0's” within that column

I have tried looking at many different potential avenues at solving this; INDEX, MATCH, DCOUNT, HLOOKUP with INDEX & MATCH, COUNTIF with INDEX & MATCH. What I'm looking for is a way to set the ...

**0**

votes

**2**answers

24 views

### Excel COUNTIF range search returning #N/A

I'm using Excel to search multiple days (my ranges) in a timesheet for hours worked (my HLOOKUP reads the value below the dept number) by multiple members of staff for different departments.
I've ...

**0**

votes

**2**answers

16 views

### Excel - formula to calculate average for variable number of rows in table?

I have a table where the value in column B is the average of some preceding values in column A.
I want to use a named range "DaysToCount" to vary the number of rows used for the average. I've tried ...

**0**

votes

**3**answers

14 views

### Excel - How to merge rows from 2 tables based on a common column?

I am trying to merge 2 worksheets where only one col matches in content.
Column"A" in both worksheets have matching values but all other fields are different.
Worksheet1
Col"A" colb colc cold
...

**0**

votes

**0**answers

31 views

### How do I make a function update dynamically when I make changes to the function in a “master” cell?

For example, column 1 would contain input data, and subsequent columns would perform actions on that data.
Row 1 would be the master row. All changes to the formulas in row 1 would be duplicated ...

**0**

votes

**1**answer

26 views

### Get row number for the row matching multiple criteria in Excel

I have 2 worksheet that work together. Worksheet "Application" has a DATE column, a CODE column (a single digit number), and a VALUE column with a dollar value.
Worksheet "Scheduled" also has a ...

**0**

votes

**2**answers

25 views

### How can I find max value for a text as unique identifier in Excel?

This is basically for an auction setup in Google spreadsheet.
Say there are 26 bidders A to Z and each bid 5 times with different values. I need to find the max value bid by each of them and display ...

**2**

votes

**2**answers

731 views

### How do I get “yes” or “no” in Excel if the information comes from “true” and “false”?

first of all, I have to point out that I am a German visiting this forum (I couldn't find anything useful on the German websites), so excuse my English if it is bad here and there ;-)
Here is my ...

**-1**

votes

**1**answer

23 views

### Excel: Use array to sum product by row

Is there a way to use SUMPRODUCT or use an array formula with arrays of differing sizes?
Currently I have a list of ~1500 products in column A
and columns B-I contain their respective component ...

**0**

votes

**2**answers

19 views

### Show image in cell based on cell value

I am creating a weekly income comparison table. I want to compare A5 and A10 cells and display appropriate image in B7 cell. The image will be either UP or DOWN arrow (which reflects increase or ...

**0**

votes

**3**answers

79 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

**1**answer

22 views

### Comparing two strings in excel, add value for common variables

I'm comparing two large datasets containing strings in excel. Column A contains the numbers 1-1,000,000. Column B contains 1,000,000 strings, neatly organized in the desired order. Column C contains ...

**1**

vote

**3**answers

43 views

### IF formula returns wrong value

https://www.dropbox.com/s/v6nd2z4icftmnvf/example1.xlsx
We have Excel 2007 (German) and vista ultimate.
I am trying Excel by building a roster but have run into a problem with the following formula.
...

**0**

votes

**2**answers

42 views

### SUM From Sheet 1 Columns to Sheet 2 Rows

Tried searching for a while, didn't see what I was looking for.
On Sheet1, I have approximately 100 columns, each representing an item. Each row is populated by a quantity of that item. As you can ...

**0**

votes

**3**answers

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

**1**

vote

**1**answer

22 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

**0**answers

10 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

**0**answers

22 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

**1**answer

35 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

**1**answer

16 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

**1**answer

23 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

**1**answer

21 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

**1**answer

25 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

**1**answer

43 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

**1**answer

23 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

**0**answers

18 views

### How to check if cell contains entry from another column?

So basically I have a column(A) with company names and addresses together. I have another sheet with a column(B) with the company names that are members. I want to check if the companies in A are ...

**0**

votes

**2**answers

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

**1**answer

23 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

**1**answer

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

**1**answer

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

**1**answer

24 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

**2**answers

14 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

**0**answers

17 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

**2**answers

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

**0**

votes

**1**answer

59 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

**0**answers

23 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

**2**answers

41 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

**1**answer

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