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

**0**

votes

**0**answers

5 views

### Perform a calculation n times and sum the results (without VBA)

I'm looking for a way to sum the result of a calculation performed a given, arbitrarily large, number of times.
Suppose I have 15,000 customers each of whom will spend between $100 and $200, and I ...

**0**

votes

**1**answer

4 views

### Formula to incorporate Highlighted text & Loop function

I'm trying to analyze over a 1000 line items,
with different information in all columns ,although certain columns might have duplicate information.
I can do conditional formatting and highlight the ...

**0**

votes

**1**answer

10 views

### Microsoft Excel 2013 on Windows 7, creating cost based on drop down values

Hi I am need of some assistance and was wondering if anyone can help me. I am creating a spreadsheet for quoting jobs on site and am stuck with a particular part.
I have a cell containing a drop down ...

**-1**

votes

**1**answer

14 views

### Assign the value of a cell as a cell reference of another cell in Excel - How to?

The intention behind this query is to simplify the review of excel sheets with engineering calculations.
Let us assume that column A contains the notations. cell A1 contains "Rfa" and cell A2 ...

**1**

vote

**2**answers

37 views

### Look up a value in a list and return ALL multiple corresponding values

The basic answer to my question is provided here: How to look up a value in a list and return multiple corresponding values. However, once the formula is entered, I need to drag down, or copy/paste ...

**-2**

votes

**1**answer

21 views

### Need an “IF” function that shows whether the difference of a simple subtraction is greater than a certain value

I have values in cells A3 and A2. I will be subtracting the value of A2 from A3. What I need is to be able to highlight or flag the result if the difference is greater than an absolute value of .01 ...

**0**

votes

**1**answer

24 views

### Spreadsheets Formula Check In/CheckOut Autopopulate Data

I am working on a spreadsheet (I am doing it in Numbers since it will easily let me work on the file on my mac, iPhone, iPad and online at icloud.com)... but this is essentially also an excel question ...

**0**

votes

**1**answer

22 views

### Auto-fill rows from columns in another sheet?

I'm using Google Doc's Spreadsheet at the moment and I'm attempting to create the same view in another sheet.
First Sheet:
(yes I need these merged cells)
Second Sheet:
Unfortunately Google ...

**1**

vote

**1**answer

17 views

### How do you auto update row count in a formula that yields a percentage?

How does one get a formula to auto update when a row is added when this formula is meant to yield as a percentage, which is directly linked to a pie chart? For example: my formula reads
...

**1**

vote

**2**answers

44 views

### Excel- How do I find common text values from multiple (>2) columns?

I have 9 columns of text data and need to know the common terms that are found in all nine columns (or possibly different combinations of columns). I can do 2 columns ...

**0**

votes

**2**answers

12 views

### How to use SUMIFS function for summing values if criteria range exists in a range?

My data:
A B C
a 3 d
b 1 a
c 8 e
d 5
I want to use SUMIFS function, sum range is B1:B4, if the corresponding value in column A exists in column C, sum the values in column ...

**0**

votes

**1**answer

23 views

### Ignoring value IF “[Value_if_false]”

I am trying to figure out the average difference in monthly sales versus previous years, and I have an IF function within an Average function. I want to skip zero values in the average function if the ...

**0**

votes

**0**answers

5 views

### Menu option Insert -> Script… is missing in my Spreadsheet, how do I fix it? [migrated]

I've 2 documents, in 1st one I was able to add 'Script...' (as suggested here) from the Insert menu, but on the 2nd one and all new which I'm creating I don't have this option present.
Why this ...

**2**

votes

**1**answer

35 views

### Can I use table relationships in formulas?

Are table relationships only for pivot tables & charts, or can they be used in formulas too? The introduction of tables made it easy to write really readable sumifs like this:
...

**2**

votes

**2**answers

26 views

### I would like to copy data from 1 row to another in Excel

I am trying to solve the following issue in excel: I am working in two sheets, Sheet 1 and Sheet 2. There is a column of data in sheet 1 (say from A5 to A20). I need every row from that column to be ...

**0**

votes

**1**answer

16 views

### Excel 2013. Address from Value

I have two spreadsheets, one with a Calendar, and the other one with Data Chart.
I'm trying to make it so that the Data Chart will only start using Data from Today's date in the Calendar. (Note: ...

**2**

votes

**1**answer

33 views

### What does “--” used in Excel function mean?

I came across this reference in a spreadsheet I'm looking at and don't understand what it means.
=SUMPRODUCT(--NOT((ISBLANK(CONSTRUCT))))
Can anyone tell me what the -- in front of NOT refers to?
...

**-1**

votes

**0**answers

20 views

### Excel 2010, IFERROR, INDEX, MATCH

I've got a sheet consisting of various lines like the following.
POH-00006366 CYM001 Cymot NAD 6000
POH-00006366 CYM001 Cymot NAD 7000
POH-00006366 CYM001 Cymot NAD 8000
...

**0**

votes

**1**answer

28 views

### Excel 2010 Cell Formatting

I have a crew schedule made up in excel. There are 4 different shifts A B C or D. There are 5 people on the schedule, and the schedule is laid out like this in excel:
Each person works the same ...

**0**

votes

**0**answers

35 views

### comparing two columns for matches then return values meeting certain conditions

I have the following:
column D ( 300 values representing the average of three cells, A1:A300 & B1:B300 & C1:C300)
column H ( 50 values )
column E & column F & Column G; they are to ...

**2**

votes

**2**answers

26 views

### Problems figuring out weekly scheduling and hours past midnight

This is a screenshot of what I have
What I'm trying to do is have the hours for each individual person posted as shown or a way that the hours can be posted like this and then have the total amount ...

**0**

votes

**1**answer

31 views

### Make the background/text colour follow another cells background or text colour

Ok so I have a large spreadsheet (so I don't want to have to set each cells colour individually or manually) and I have many cells copying other cells. So for instance I have cells N9, N
3, N80, N117 ...

**0**

votes

**0**answers

21 views

### 2 EXCEL (array?) questions

I am trying to perform some lookup calculations on a pivot table and having some abysmal luck finding the correct function/formulas to use. I've searched the Interwebs and can't find something that ...

**-1**

votes

**0**answers

15 views

### Selecting values from different rows in an Excel 2010 table and merge them in order in a sinlge row [on hold]

I want to use a formula, or set of formulas, for selecting values from a data table were the number of values differ between rows (see attachment). I do not want to cut and paste or use any commands ...

**1**

vote

**2**answers

14 views

### OOO: Increment sheets for fill down

I have a row of cells on "sheet1" referencing a set of cells on a different sheet. I want to fill down identical sets of cells while incrementing only the sheet.
Another way to describe the problem: ...

**-1**

votes

**1**answer

17 views

### In Excel 2013 Is there a way to code an auto-complete function ibased on values in another tab

I want to create an auto complete cell in Excel for the resource name. The list is too long for a custom list and I really do not want a drop down list as in data validation. Does someone have an ...

**0**

votes

**1**answer

18 views

### Looking for a formula(s) that will return the next date (higher than the starting date) from a row of dates, then the next date from that one

Worksheet 1 lists all of the "legs" flown by our aircraft. The leg or legs for each day would then go to Worksheet 2 for Day 1, Worksheet 3 for Day 2, and so on. Legs are listed in columns starting ...

**-2**

votes

**0**answers

14 views

### Search for multiple variables in multiples rows at the same time in Google Sheets/Drive [migrated]

I'm using Google Sheets (only option, sorry, know this should be a database).
Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to.
Sheet 2: List of 50 ...

**0**

votes

**1**answer

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

**-2**

votes

**1**answer

27 views

### Display formula result only if inputs are defined

I have a simple spreadsheet, with one row of equations to get sums or differences. What I'm trying to do is enter an equation so that if only one part of the end equation is present, the field is left ...

**-1**

votes

**0**answers

20 views

### MS Excel 2007 Copy Range Across Worksheets and Increment or Offset by 50 rows

I'm looking to copy and paste a list of names from Sheet 1 to Sheet 2 but space them out. For example, I'd like Sheet1!A6 -> Sheet2!A51, Sheet1!A7 -> Sheet2!A101.... Sheet1!A41 -> ...

**0**

votes

**0**answers

12 views

### How can I calculate the grouped averages and maximums per day for a range of dates and times?

I have a CSV dump describing the utilisation percentage of some servers in my organisation, covering data samples from several times per day. Each server belongs to a group. For each group, I have to ...

**0**

votes

**1**answer

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

**-1**

votes

**1**answer

25 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

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

**1**

vote

**0**answers

30 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

32 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

30 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

23 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

**1**answer

17 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

19 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

57 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

45 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

26 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

33 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

27 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

**4**answers

30 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

35 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

37 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

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