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

**0**

votes

**1**answer

72 views

### Reverse vlookup

I have a list of descriptions and I want to add a code column as shown below. The descriptions in the list are neither unique nor sorted.
I have an index table with substrings and the codes in ...

**5**

votes

**2**answers

4k views

### Excel: Counting number of MAX values in a range

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet:
A B C D
1 Round | Rita | Sue | Bob
------+------+-----+-----
2 1 | 5 | 3 | 4
3 ...

**0**

votes

**0**answers

53 views

### How to select random people for a match depending on many criteria?

I am stuck in a situation where I need to organize fight match in my school.
I was doing it manually but now thinking to automate it via excel. Automation will reduce my work & also the random ...

**0**

votes

**0**answers

94 views

### Automatically input range of numbers in a single cell and return outputs check every time when changes made in workbook

Input all values of Input column in enter time block and it will calculate sog(of all inputs) and paste it in output column run the program whenever any changes are made in workbook SOG is shortage of ...

**0**

votes

**1**answer

43 views

### Excel: Get sum if value of another column is non-blank?

I think this is really simple and a duplicate. However, much Googling hasn't helped me find the answer. So I thought I'd ask a nice clear StackExchange question, to help people using the same search ...

**0**

votes

**1**answer

260 views

### Concatenate values for matching items in Excel

I have more than a thousand rows of data like this:
I need to concatenate the values into comma delimited strings for matching items in the list. So, for example, the result for Item 1 would look ...

**0**

votes

**1**answer

85 views

### How to automatically increment a value in Excel?

Basically I'm trying to make a formula that will put the number 0.25 in a column and then add 0.25 until it gets to 30. So basically I need it to look like:
0.25
0.5
0.75
1
1.25 etc.
Been ...

**2**

votes

**3**answers

64 views

### Error subtracting times

I am trying to subtract a PM time and AM in Excel 2013. I am getting the ##### error.
Example:
A1=7:00 PM B2=12:30 AM
=+A1-B2
I have the cell formatted in time, but it comes back with the error....

**0**

votes

**0**answers

27 views

### How do I find how many times two rows share a value across multiple columns in Excel?

I have a table, something like this:
Example A
Where I have a list of zip codes down column A and then another 50 or so columns (ex: A-D) that contain values. The value is entered in each cell ...

**1**

vote

**3**answers

218 views

### Counting occurrences of a value, two columns

I want to count the occurrences of values >=13.8 in two columns, no problem with that - but if both of the values on the same row is >=13.8 I want it to count as one match.
Here's an example
...

**0**

votes

**0**answers

29 views

### Excel dynamic range selection and look up

There is a list of unique identifiers. The list is dynamic (one day it has 7 and another day it has 17).
Based on this list, we download data related to those identifiers in another sheet.
This ...

**1**

vote

**1**answer

240 views

### How to “merge” multiple rows in one column while keeping the rest of rows separate? Excel 2010

I have several rows with different data. One of my columns is for names. How can I merge the cells with the same name, so there is only one cell with the name up front, and several rows with the ...

**0**

votes

**1**answer

21 views

### Excel LEFT and IF

Mostly all fields begin with the name and address, but some fields start with a number. I would like to have the address removed leaving only the full name, which I do have the formula and it works ...

**0**

votes

**2**answers

53 views

### How can I add all previous cells to a cell using a function in Microsoft Excel?

If I have a column:
0 |
1 |
2 |
3 |
4 |
I would like for in the column on the right to take a cell on the left and add all previous cells to it. So that it looks like the two columns ...

**0**

votes

**1**answer

93 views

### How to insert Time in a range of excel sheet which should not update automatically?

Friends I want to insert time in a range which should not get updated automatically. For example if there is any value in cell no k5 then the current time should automatically be inserted in cell no ...

**0**

votes

**0**answers

105 views

### Listbox, mouse wheel scroll issue

Whenever I try scrolling through list Box created by Excel Vba , using mouse wheel . Excel sheet tend to freeze up .
have any one experienced the same ?
Excel version that I am using is 2007.

**0**

votes

**0**answers

100 views

### Excel Highlight cells with matching text when hovering over cell

I have an excel sheet showing a College course progression, When I hover over a cell with a class name in them, I want cells matching the names of the dependent classes to be highlighted, what's going ...

**0**

votes

**0**answers

10 views

### Why is my default templet (xlstart/book or /sheet) not used when I import data

Why is my default template (xlstart/book or /sheet) not used when I import data.
I set the default workbook to format the first column as text, or as a custom format (seventeen zeros) but when I ...

**0**

votes

**1**answer

938 views

### EXCEL: How to Search multiple files for value corresponding to text in same row?

I know this is a blatant help me post, but I have been given this task and I don't know how to get it done in time.
I have moderate knowledge of excel, but I dont know any VBA. I have learnt C++ so ...

**3**

votes

**1**answer

21 views

### Formatting number within text

I have the following formula
=concatenate("Price is ", A1," of new price")
where the value "A1" is the percentage value.
What should I write in the formula so the percentage value of A1 appears as ...

**0**

votes

**0**answers

12 views

### Excel Adding comments from current worksheet to new imported data that has changed

I import data daily, format it, add notes in multiple columns, and save this. The next day, a new import is run (data will have changed), formatted and saved under a new name. I need to get the ...

**2**

votes

**2**answers

17k views

### Excel formula to get time difference as a decimal number

I have a table like this (the C column is blank):
A B C
1 19:30 23:00 (3.50)
2 14:15 18:30 (4.25)
I need to calculate the time difference in each ...

**1**

vote

**4**answers

1k views

### Excel - how to drag down formula while keeping horizontal cell reference continuity

I been trying few formulas and google search, but wasn't able to find anything that would help my problem. To keep it simple, as explained in the attached picture, how do I drag down formula ...

**0**

votes

**2**answers

59 views

### How to keep a columns formula applied to a newly added row?

I have a recurring formula down an entire column but every time I add a row the formula isn't applied to those new cells in that column. How can I make the formula permanent so it is consistently ...

**1**

vote

**1**answer

204 views

### How to count number of cells over multiple worksheets whose values are in between a given range?

I have an Excel workbook with numerous worksheets containing data like:
'DATA_1' 'DATA_2' 'DATA_3'
A B A ...

**1**

vote

**1**answer

215 views

### How to simplify Excel/Google Sheets formula with redundant method calls?

I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from ...

**0**

votes

**1**answer

32 views

### Excel get the range of Cells beneath a cell that match criteria

0. A, B, C
1. Level, Name, Std Dev
2. -1, Total, 88.80
3. 0, Commodity, 367.31
4. 1, Option, 582.81
5. 2, DBA, 582.81
6....

**3**

votes

**1**answer

137 views

### IF formula in Excel to calculate various outputs, defined by days between 2 dates

I'm trying to write an IF formula to calculate 4 types of data based on 2 dates.
Example:
Cell I4 (Install Start)
Data entered here, eg 20/10/2015
Cell J4 (Install End)
Date entered here, eg 25/10/...

**1**

vote

**2**answers

28k views

### How to identify cells in Column A that contain any of the values in Column B

First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches.
My question is very similar to Find if ...

**2**

votes

**1**answer

39 views

### Combining OR with AND in Excel

I'm trying to write a formula in Excel 2016 which outputs a "Yes" if the following two conditions are met:
A2 value begins with 4 OR 9
A2 value length is 8
Otherwise output "No".
This is the ...

**0**

votes

**1**answer

31 views

### Excel Lookup for work sheet comparison

I have 3 sheets. The first one contains data for Shop A, the second data for Shop B, and the last sheet contains the results.
Sheet A & B: Item Code, Name, Brand,and Stock
I want to compare ...

**1**

vote

**1**answer

38 views

### Repeating values - Microsoft Excel [closed]

I am provided a large spreadsheet monthly that requires up to 6 instances of the same customer ID to be entered manually. I would like to design a Macro or VBA code that will take my customer list and ...

**3**

votes

**1**answer

705 views

### Excel 2007 Function, last three items

My question is twofold, so bear with the wall of text. I'm making sort of a banking spreadsheet. I will input income/expenses in four columns (Date/Amount/Type/Description) and I need it to keep track ...

**2**

votes

**1**answer

159 views

### How to make Excel increment by a large number (I want the values of cells D4,D100,D196,D292, etc)

I have ~35000 data points, and I want to sum data points 4-99,100-195,196-291, etc. So basically I want 365 sums of 96 data points each.
I managed to use the IF function logic to print the sums I ...

**0**

votes

**1**answer

217 views

### how to generate serial number for each selection in combobox in excel vba

Private Sub CommandButton1_Click()
With ComboBox1.Value
Dim a, b As String
Dim i, j, k, l, x, q, m, temp As Long
a = ComboBox1.Text
i = Application....

**0**

votes

**1**answer

52 views

### Excel, Lookup output colum depending on another cell

I currently have lookup function that takes a code, looks for it on a specific column and gives me a price value on a specific column based on the code. I use this function many times, for different ...

**2**

votes

**4**answers

325 views

### Is there any formula which should insert time which should not auto update in the excel sheet?

I want to know is there any formula except =NOW() to insert time in an excel sheet also which should not update time automatically?
The formula =NOW() is working fine but the problem is that it is ...

**0**

votes

**1**answer

114 views

### How do I delete repeated values (#Not remove duplicate values#) in a column excel?

I have the following two columns of data:
Location Value
A 1
A 1
B 2
C 2
D 3
D 3
E 4
E 4
F ...

**3**

votes

**5**answers

32k views

### Excel - Delete specific word in each row?

Is there a way to have Excel delete a sepcific word in every row? For example, each row says Mr. and Mrs. {first name} {last name}, and I want to delete the {first name} from each one. (Or ...

**0**

votes

**1**answer

25 views

### creating a functional array to store names of files in a folder

i want to create a functional array that stores all name of file in perticular folder
this is what i tried..
Function number_of_file(i As Integer) As Variant
Dim file_object As Object
Dim ...

**0**

votes

**0**answers

27 views

### Rounding up from a list of values / Returning a value from a table in Excel

I have already worked out 1 variable in a Spreadsheet (In this case it's the "AREA OF STEEL REINFORCEMENT") but it needs rounding up to a specific value given in a table.
Firstly though an input ...

**0**

votes

**1**answer

49 views

### Split Excel Cell by last Delimiter and get Text to left

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

**3**

votes

**1**answer

101 views

### I need an elegant way to decode a series of codes into a single Excel cell

We have a cell that's filled with up to five comma delimited two letter codes. There are 20 possible codes. I would like to create a single cell that contains a "decode" of those two letter codes. ...

**1**

vote

**2**answers

158 views

### COUNTIF and SUM in Excel with different ranges

Is there a more efficient way of making the following function?:
>=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0")
I want to count if the cell is bigger than 0 ...

**0**

votes

**1**answer

42 views

### Changing formula fill step

I'm trying to make a formula from one sheet (Sheet 2) which calculates the minimum value of a given block of cells in another (Sheet 1). The block is of consistent size.
When I drag the formula down ...

**0**

votes

**3**answers

184 views

### How can I get the unique values present in a column for each set of identical values in another column?

I have the following two columns:
Con.By Prod
A 1
A 1
A 2
A 2
B 1
B 1
B 2
B 2
I can easily get the unique values either in column Con.By or ...

**6**

votes

**1**answer

265 views

### Use functions like Countif, Countblank etc. to refer to discontiguous ranges.

Is there a way to use array formulas or some other feature to essentially have an excel function that means.
=Countblank({A1,A4:A6,A112:127,B29:B38}) etc.
Essentially, I want to refer to multiple ...

**1**

vote

**3**answers

298 views

### How can I compare what data value changed over two periods?

For my Fantasy Football League I'd like to quickly be able to compare the data of two data sets to see who moved up/down the table over a week period (two different sets of data).
Here's an example ...

**1**

vote

**1**answer

55 views

### Spreadsheet formula to display most recent prices

I have created a LibreOffice Calc spreadsheet with columns A-C. In column A, I have a list of parts purchased (in chronologically ascending order). In column B, I have a list of corresponding prices....

**11**

votes

**2**answers

11k views

### In Excel, can I provide a “default value” in case my formula returns #NA?

For example,
Say the value of a cell is:
IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,...