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

**-4**

votes

**1**answer

30 views

### Why am I getting a #Value error with my IF statement?

I keep getting an #VALUE error with my IF statement in Excel. Any ideas why? Any help is greatly appreciated!
=IF(
OR(
R2 > 3
),
0,
( IF(
R2 = 1,
...

**1**

vote

**2**answers

28 views

### How do you properly align average and standard deviation outputs in a spreadsheet?

I am not sure of the best way of formulating this question right now so I will use an example using random numbers. I start with values assigned to IDs, so that each ID can be n=1, n=2, ... etc
ID ...

**1**

vote

**1**answer

13 views

### Automatically update date in excel cell based on system date

I have data in an excel cell like this: 11/2/2015 6:05:00 AM
I need to update this as follows:System Date+1 6:05:00 AM
What function can be used so that whenever sheet is opened up, based on system ...

**3**

votes

**3**answers

205 views

### Split Excel Cell by last Delimiter

I need to split a cell by the first delimter (reading right to left) so that I can put that value in another cell.
For Example, my data in cell A1 could look like:
"something - more something - a ...

**0**

votes

**0**answers

16 views

### Excel formula weekdays

I am trying to create a formula that will give me the expected due date of a task (15 business days), and that needs to exclude weekends, that its easy and I did it with a simple weekday formula:
...

**1**

vote

**0**answers

19 views

### 10 minutes to calculate the very simplest excel formula - why?

Well I know partly why, my spreadsheet has about 180,000 rows. But that has not presented a problem for sorting or filtering.
But when I entered =B2 in cell C2 and stretched it down to the bottom, it ...

**-1**

votes

**0**answers

16 views

### How to calculate and input data from one excel sheet to another [on hold]

I want to be able to input data daily onto the first sheet of my excel file for six different things. I then want to have a separate sheet for each thing that will then show the daily value inputted ...

**1**

vote

**0**answers

12 views

### Excel 2010/2013 - Need to count number of doses of a medication given on the same date, to the same patient MR#

Okay, a colleague is trying to compile some dosage information. He has a spreadsheet containing Dates of Service, the patient's Medical Record #, and the name of the Medication. So, if Oxycodone ...

**0**

votes

**1**answer

17 views

### Select data from different sheets in LibreOffice Calc

I have three sheets 1,2 and 3. There are three columns on sheet 2 and 3: name (A), surname (B), age(C). How can I select men whose age is >=30 from sheet 2 and 3, order them by age and show the result ...

**0**

votes

**2**answers

22 views

### How can I filter out only those values that have appeared more than once in a column?

I have the following data to start with:
Sr. No Conf. By Prod
1 A X
2 B Y
3 A Z
4 B X
5 A X
6 ...

**3**

votes

**1**answer

20 views

### How to average ten cells below when rows are inserted

This is a question about Excel, not VBA. I have a simple formula in my top row that averages the ten cells below it. I want to copy the row below this one and insert a copy as a new row. (My range is ...

**0**

votes

**1**answer

12 views

### Formula that execute lookup on first match of a criteria

In cell C26, I want to use a formula to print the earliest date at which: Consolidated target > Total # of skus
Thanks in advance for your help!

**0**

votes

**0**answers

50 views

### How can I auto increment only columns reference in formula?

I have the following formula:
=COUNTIFS(Sheet1!$A:$A;"=Women";Sheet1!E:E;"1")
I'm working in Sheet2. Suppose I have in Sheet1 the following table:
| A | B | C | D | E |
...

**1**

vote

**2**answers

90 views

### Interpolate a collection of values to expand the number of points in the pattern in Excel

Suppose I have six cells (A1:A6), containing the values 1, 2, 4, 8, 16 and 32.
These values form a pattern, which I can graph. I want to expand the list of values to include intermediate ones ...

**0**

votes

**1**answer

12 views

### Trying to show in one cell the date at which a cash flow will become negative

I made a cash flow projections sheet with columns going from D to FE and Rows going from 1 to 65.
On the top of each column I have a row with dates from D8 to FE8.
On the bottom I have a row with ...

**0**

votes

**1**answer

36 views

### Can I have two LOOKUP functions within a single IF function in Excel?

I am attempting to organize a dashboard of sorts that will place the name of the appropriate owner in a column on the spreadsheet. The two criteria is the location or US state the item is located in ...

**0**

votes

**2**answers

15 views

### Excel Indirect #ref error

Trying to use the INDIRECT formula to pull a summary at the end of a large workbook. I have listed the Worksheet names in a single tab and now wish to use these to pull specific cell data into a ...

**2**

votes

**1**answer

31 views

### Adding Value to cells in Excel

For the spreadsheet pictured below, I am trying to create a formula that will calculate the amount of shirts ordered for each size, then calculate the cost of each shirt ordered for a balance (total) ...

**1**

vote

**0**answers

17 views

### Control Number Generation

So currently I have the following. When I click a button or hit submit button The form looks on the Ledger Tab. I was trying to get the form to automatically generate a control number (ConNum) that ...

**0**

votes

**0**answers

15 views

### Ledger Balance Sheet Assitance

Need your help again....I am building a ledger in Excel. Tab 1 has a Form to input data into cells in tab 2. Tab 3 is Reference Information that the form pulls data from for drop down menus. On tab ...

**0**

votes

**1**answer

11 views

### How to find table_array referenced in Excel formula in cell

I was given an Excel spreadsheet that I have to convert into a SQL view.
In the cell formulas VLOOKUP is used and for almost all of the table_arrays I can find where the table_array is. But for one ...

**0**

votes

**0**answers

14 views

### Worksheet Formula Changes

='Location (33)'!A35
I have 1200 worksheets to pull in, I want the worksheet number to increase but not change the cell number, I've tried to make the cell constant and the table properties to fill ...

**-1**

votes

**0**answers

24 views

### Microsoft Excel Query

Suppose there are five columns:
A,B,C,D and E
in which:
A and C contain a combination of letters and numbers to represent a specific item and
B and D contain numbers to indicate the equivalent ...

**0**

votes

**2**answers

22 views

### Excel formula for a “running %” of a column

im trying to figure a formula for a running percentage of entries in a column.
For example, in column A, i have "r" for returns, and "n" for non returns, and "c" for canceled, but "c" is classed as a ...

**0**

votes

**0**answers

5 views

### Create a hyperlink to a cell in a “Table of Contents” sheet quickly

I have an excel document with a vast amount of sheets (upwards of 100). To make navigation easier, I made the first sheet a "Table of Contents" where I listed all of the other sheet names in a column ...

**0**

votes

**0**answers

21 views

### Excel formula needs help to create table

Trying to understand how to create a table that identifies what specific dates relate to the row and column it is in.
IE. if I have columns A, B, and C. and rows 1, 2, 3 with dates applied within ...

**0**

votes

**0**answers

7 views

### The data(Job #) in one column is only put to another spread sheet, IF another column contains data

J/C # A. B. C. D. E. F. G. H. TOTAL Estimated Time
97647 45 45 90 30 120 330
97651 15 30 45
99999 60 120 75 360 615
11111 ...

**-1**

votes

**0**answers

8 views

### Match information to a range and add corresponding data?

I've got a range (A1:b600) that contains names (a) and emails (b), and I'd like to check if the contents of another cell (D1) matches one of the names in my range. If it does I want to look for the ...

**0**

votes

**0**answers

10 views

### How do I stop column formulas from automatically calculating in an Excel Table?

When using a column formula for a table in a workbook with calculation set to manual, when tinkering with formulas, Excel calculates them if the column formula changes.
The automatic propagation of ...

**-1**

votes

**0**answers

17 views

### How to search a string with color code in many workbooks and copy row if true

How to search a string in many workbooks and copy row if true
Under this link I found a great tool to copy a row if true from a searies of workbooks and copy them into a new workbook.
My problem ...

**1**

vote

**2**answers

38 views

### Count coloured cells without a macro?

Is it possible to count cells by their colour (not by their cell value as they are blank) in Excel 2010/2013 without using VBA, as my files needs to be saved as .XLSX?

**0**

votes

**0**answers

25 views

### Vba Script to replace html tag with formatting

I am looking for a vba script, which replaces a html <strong>.
Example <strong>Any Text here shall be bold</strong>
The result shall be Any Text here shall be bold.
It shall macth ...

**0**

votes

**0**answers

24 views

### If 2 checkboxes true then add cell value - Excel Formula

I'm trying to add a list of prices depending on if 2 check boxes are ticked.
What I want to do is say if the paid checkbox is ticked and the just eat tickbox is ticked then add get the number in the ...

**1**

vote

**1**answer

30 views

### Change every character in a cell with another in Excel

I have a large list of phrases that I would like to make more disguised. I want to substitute each letter/character with another character and be able to decode this with another formula so I am able ...

**0**

votes

**1**answer

29 views

### Multiplying the remaining balance of an amount

I want to multiply the total of an array of cells, but only want to multiply the remaining number of the overage amount.
For example:
Goal amount: 10,000
Sum: 15,000
Multiply by: 5,000

**0**

votes

**2**answers

31 views

### How can I select and output the first and last row of a worksheet or a filtered list?

I am new to VBA and I have no clue where to start with this.
I have two columns of data:
Team Member Time
A 1
A 2
A 3
A 4
I am only interested in ...

**0**

votes

**2**answers

32 views

### Check if any names are present in two columns [duplicate]

I have two lists on names, first list of names just under 140k (Column A), second under just over 7k (Column B).
I want to see if there any matches from column A & B.
My Excel skills are basic ...

**1**

vote

**1**answer

25 views

### Look up a name from row in a timetable and output the column header in Excel

I'm making a timetable for interviews which comprise of 6 stations plus a rest.
(FYI in the timetable time is progressing downwards)
Sample of timetable with names omitted:
I'd like to create ...

**2**

votes

**0**answers

31 views

### Is there any way to include a comment in a referenced cell in Excel?

I have two Excel spreadsheets:
The master sheet which has tables of data
A second sheet which references certain cells from the master sheet in order to collate certain data.
I wanted to know if ...

**0**

votes

**1**answer

27 views

### I just cant figure out the formula for this excel sheet

i hope someone can help me with a formula for excel.
On my sheet i have on column A - D's for Dog, and C's for Cat, and O, for other.
I'm trying to figure out the formula for: If 8 or more of the ...

**2**

votes

**1**answer

31 views

### Increase a cell's value based on other cell's value

What I am looking for is an "IF" formula that can solve my problem:
I have a single sheet with 2 columns and an unlimited number of rows.
On the first column (Column#1), I have a drop-down with ...

**3**

votes

**1**answer

36 views

### In Excel, how would I calculate the number of Mondays between dates that excludes a list of holidays?

I'm currently using this formula:
=INT((WEEKDAY(E2-2)-E2+E3)/7).
I know how to use the NETWORKDAYS function, which allows you to a specify a list of holiday dates, but I can't figure out how to ...

**1**

vote

**1**answer

28 views

### Retrive row number of MIN IF formula

I would like to know the row being used in a MIN(IF()) formula so I can use it to grab a another value in the same row..
I have this formula:
Between the two sheets
A is an Id
B is a Date
C is ...

**0**

votes

**1**answer

12 views

### transposing and collating text from different sheets into separate sheet

I am using the following formula to collate text from 3 sheets into a 4th.
=$'sheet 1'.A1&CHAR(10)&$'sheet 2'.A1&CHAR(10)&$'sheet 3'.A1&CHAR(10)
this collates the text with a ...

**1**

vote

**1**answer

30 views

### Applying text formula only if text is in the cell in Excel

I am using 4 Excel sheets for collating text.
The formula I currently have in sheet 4 is:
=$Sheet1!A1&CHAR(10)&$Sheet2!A1&CHAR(10)&$Sheet3!A1&CHAR(10)
This allows me to write ...

**-1**

votes

**1**answer

24 views

### Auto-populate calendar with an “X” in Excel

I have have a set of due dates for preventative maintenance on equipment and I want a calendar to auto-populate with an "X" under the correct month it is due.
For example I have machine 1 due 1/1. ...

**0**

votes

**1**answer

21 views

### How to use conditional formatting to search a specific value in a range in ms excel?

I have values in the range K5:K333. I want the value in cell S5 be searched in the range and the corresponding cells of the range being formatted.
I am using conditional formatting's "use a formula ...

**0**

votes

**2**answers

24 views

### If multiple cells are with the same text, return this text(value) to another cell

I need your help with this:
If A1:E1 is "Red", return "Red" to C3;
If A1:E1 is "Blue", return "Blue" to C3;
BUT
If A1:E1 are not the same, return False or "X" to C3
What function to use ...

**-2**

votes

**0**answers

16 views

### Excel vlookup multiple value return [on hold]

I am shooting a short film and i need to allocate shots to be edited to my other 4 friends.
My initial approach is to have a master sheet where all the shots are kept and then a column headed ...

**0**

votes

**1**answer

26 views

### Prefix/Suffix Data Validation List

I'm finding it really difficult to understand how to prefix or suffix a set of words based on a what I have chosen in a 'Data Validation List'.
CLICK HERE TO SEE/DOWNLOAD AN EXAMPLE SPREADSHEET
I ...