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

learn more… | top users | synonyms (5)

0
votes
1answer
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
2answers
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
3answers
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
0answers
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
3answers
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
2answers
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
4answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
4answers
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
1answer
114 views
3
votes
5answers
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
3answers
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
1answer
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
2answers
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,...