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

learn more… | top users | synonyms (5)

0
votes
2answers
17 views

Excel - How to return the first number only string that is between 7-10 characters long in a large body of text? (preferably Formulas only)

I'm trying to extract some numbers from a few cells that each have a large body of text. The number strings are accompanied by certain words that precede the number string I'm trying to extract. ...
0
votes
1answer
13 views

Lock Drop Down Cells and Enable Use - Excel 2013

I would like a drop down list in a cell. I would like to lock the cell so it cannot be moved or altered but changes other than selecting from the drop down menu.
0
votes
1answer
20 views

If < formula is not working on all my cells

I need to determine if my players play slots or tables based on if they have higher slot or table points.I typed in the formula =IF(D2
2
votes
3answers
33 views

Excel Formula-Return Value based on looking at two separate values (either/or)

I am using this formula to return a value from table below: =IF(ISNA(VLOOKUP(A2,USUB,1,FALSE)),"No Fruit Found",VLOOKUP(A2,USUB,2,FALSE)) USUB table has this data: Fruit Fruit Code ...
0
votes
3answers
18 views

Variable sliding window in Excel

I am using Excel 2010, but I presume this would be solved the same in any version of Excel. I have data that I would like to sum - essentially providing a sliding cumulative value. For example, on a ...
0
votes
0answers
12 views

Creating an Excel timetable

I have created course timetables for groups of students. i.e. a timetable on a sheet for each day, with the students' names across the top. What I would like to do now is to combine the five days into ...
-3
votes
0answers
22 views

How to create a macro that pulls data into one summary sheet from multiple sheets? [on hold]

How do I create a macro that pulls data into one summary sheet from multiple sheets? I need the macro to filter on one column (the status column) in every sheet, and not bring over the rows that have ...
0
votes
1answer
26 views

Need to Reference multiple sheets with IF(OR(AND( imbeded

I need to reference 3 sheets, "Shipping", "Equipment", and "Inventory", where Column A in Shipping and Column A in Equipment both refer to the Location Number, and Equipment Type is designated by text ...
0
votes
1answer
14 views

Excel - Multiplying daily values for each week

What I have: over 200 rows, each with three values, each for a unique day. Only values for workdays are listed, so sometimes there may be days or even whole weeks missing from the list because of ...
-2
votes
0answers
24 views

Automatically Delete Incorrectly Spelled Words from Excel Spreadsheet [duplicate]

I have an Excel spreadsheet which consists of thousands of word entries. I would like to have words that are spelled incorrectly, according to the Oxford Dictionary, be highlighted in the Excel ...
-1
votes
2answers
38 views

Automatically removing invalid or gramatically incorrect entries in Excel

I need to clear out all the incorrect data from my Excel sheet, such as grammatical mistakes and invalid entries, and I want to do it automatically. I have tried the sort and filter option, but it ...
0
votes
1answer
9 views

Allow Excel user to select operator for formula

I'd like to setup a spreadsheet that would let the user select the operator to apply to other cells. For example: If A1 has 5 and A2 has 10, I'd like the user to be able to select the plus or minus ...
0
votes
2answers
19 views

If a range contains specific value, how to produce a percentage of that range below the specific value in Excel

I'm stumped. I am arranging a data set that contains ranges of numbers that are specified by two columns, the minimum and maximum values (sometimes these values are the same). I am interested in the ...
1
vote
1answer
33 views

Generating Repeating Data

I have 4 data items in column B. Initially I needed to fill another column with the original data repeated 7 times. So the first item should appear 7 times, then the second item should then appear 7 ...
1
vote
0answers
27 views

Excel doesn't create structured formula for all tables

Structured formula case I am facing 2 weird issues with Excel 2013. I have been mainly using the tables. Initially when I created the sheet and inserted tables and added formulas, they were all using ...
0
votes
1answer
33 views

Spreadsheet formula for adding all values adjecent to multiple instances of text cells

Given a table with unique and duplicate text values in a column with number values adjacent in the following format: A|12 B|23 C|98 A|66 D|86 E|3 B|25 A|65 . . . The values are simplified for the ...
0
votes
1answer
33 views

Creating a formula that analyzes whether or not individual was served between two dates

I am writing a grant report in which I need to count the number of individuals who received a service during a specific quarter. That is, if the person was in our shelter any day between 10/1/2015 and ...
0
votes
1answer
14 views

Excel indirect function to name a sheet

I am attempting to have the worksheet of an external workbook update to a reference cell on a current work book. This is my code so far. =INDEX([Test.xlsx]indirect("I8")!$A$3:$B$7, ...
0
votes
1answer
20 views

Formula not referencing new row

In Excel 2016 I have a workbook with two tabs - Active_Directory_Locations SuperTab In SuberTab I use the following formula, copied in to cells A5-A1000 - =IF(OR(Active_Directory_Locations!$D5 = ...
0
votes
1answer
24 views

Make a column hidden by a function in excel

I want to hide a column by using a function. More clearly, what I am trying to implement is, for a particular type of data bundle, which is arranged as column1, column2, column3, etc, a number is ...
0
votes
2answers
43 views

How to count rows with two values in any of the many columns in Excel?

Say I have some Excel columns that look like this: A B C D 1 Amy Bob Carol David 2 Carol David David Carol 3 Bob Carol Amy Amy 4 David Amy ...
0
votes
3answers
29 views

Leaving a cell blank in excel, or display a different cell

I've tried to read the other posts and follow but I just can't wrap my head around this formula. I would like F9 to: be blank if no data is entered in D9 if data is entered in E9, then D9*E9 if ...
0
votes
1answer
39 views

Microsoft Excel calculation inquiry

I have a distribution spreadsheet that has a "total previous distributions" column showing the (obviously) previous distribution for each partner. This amount is manually update by adding last ...
0
votes
1answer
33 views

Excel Dynamic Chart- Range Name based on IF formula not accepted as 'Series Name'

I have created a simple Dynamic Line Chart in Excel 2013 as seen below. As I change the Year in Cell D1, the Series on Y Axis changes accordingly. The X Axis series is static. When I use Excel Range ...
0
votes
3answers
39 views

Excel 2010 Specific cells for formula input cell arguements

Col A Col B Col C Row 1 a a a Row 2 a a a Row 3 a a a I am trying to set a specific range in the COUNTIF function, =COUNTIF(range,criteria). Example ...
0
votes
0answers
24 views

Cross sheet trace dependents not working when using IF TODAY formula

Here's how to reproduce the issue: Put the following formula in Sheet1, cell A1: =IF(TODAY()>0,Sheet2!A1,A2) Trace precedents works fine, trace dependends from A2 also works. However the cell ...
0
votes
2answers
28 views

How to make excel SUMUP values of multiple rowkeys in a two-dimensional lookup to return sumed up values in a corresponding cell

So I have this challenge with a set of data arrangement and by merging the VLOOKUP and MATCH strings I was able to get my result but there is a little hiccup! Pasted below is the merged string that ...
0
votes
0answers
31 views

Excel - Is it possible to create a price calculator that has varying prices for different contract terms? [closed]

I am wanting to create a price calculator using drop downs where one of the drop downs is contract term. The price per service varies depending on the length of the contract, so when the drop down for ...
0
votes
1answer
18 views

Reference cell on another sheet dynamically?

Basically I have a column of tab/sheet names. I then want to build hyperlinks to those sheets and make the friendly name a value from the target sheet. I have the first part down, thanks to a ...
0
votes
1answer
28 views

Excel formula based on date variables

I'm trying to create a fee calculator but I can't seem to get the formulas to work for me. I'm essentially trying to write one long formula that takes into account all of the different date variables. ...
0
votes
2answers
24 views

Subtract Two Cells. Result value

I want to subtract two numbers. The numbers are categorized, e.g. cell N2 (WAS) and N3 (IS). The cells populated are N1 thru N351. I want to calculate the difference between the two cells. If the N2 ...
-2
votes
1answer
16 views

MS Excel 2010 Identify peaks in a data set, select its previous values and get a chart

I have a set of data about with approximatively 25500 cells, all contained in one column. The data has a series of peaks that occurs, and I need to identify the 2nd peak (and the 4th, the 6th, and so ...
1
vote
1answer
17 views

Excel/Google Sheets: How to have a conditional multiplier

I have a multisheet that I need to do the following: If 'UserInput'!A9=1, multiply the following formula's output by 1.5: 'Data'!A1*(('Data'!A2*'Data'!A3)+'Data'!A4) [The sheet and cell names ...
0
votes
1answer
26 views

Copy distinct string by by matching items that meet a criterion

I need to copy a value when two criteria are met in two tables. Table A contains name of District, name of municipality, and code of municipality. Table B contains name of district, code of ...
1
vote
0answers
9 views

Plot a chart directly from a function in Microsoft Excel

I need an help with chart plotting in Excel (I'm using Excel 2016) let's say we have a simple function like: y=1/x Right now, to plot a chart based on this function I use two columns. In the left ...
0
votes
2answers
12 views

Can I enter data into an Excel cell as a formula, but keep only the resulting value?

I have data to enter into cells that is the result of some basic addition. Rather than screwing up the addition in my head, I'd like to just type the addition into a cell, but without keeping the ...
0
votes
1answer
43 views

Excel If formula to vba

I have this formula which checks a cell to see if its a 6,2,a, or b, in which case the word cash is written. ...
0
votes
1answer
23 views

How can I SUMIF when two conditions need to be met?

Say I have countries, values and status of the value and I want to sum the values when they relate to a particular country and are "Approved", what is the formula I would use? Column A-C is the data ...
0
votes
1answer
27 views

Formula or VBA for cell formatting using value based on a cell but formatting from an other cell

I have a fixed set of values, (0%,20%,40%,60%,80% and 100%) in column E. If I fill a cell in column E with any of the above values, the cell G should pick formatting from another cell. For example, ...
0
votes
2answers
47 views

How to check if two cells have the same row in excel?

I'm working on a huge file that gets data from a spreadsheet. I need to match the store number and the store name and return the correct address. This would be easy but a lot of the store numbers ...
0
votes
3answers
86 views

How can can I combine two IF statement in Excel?

How can I combine two IF statement in Excel? The first IF is checking a start date (in the MMDDYY format) from one cell (Staff!$G5) and if that date is greater than or equal to a date (in the ...
0
votes
1answer
28 views

Using ARRAYFORMULA to auto populate a column for rows that contain data

The title just about says it all. We have a spreadsheet that has a date in Column A and then in Column B it compares the value of Column A with today's date and spits out the number of weeks between ...
0
votes
1answer
33 views

How to Display a Formula Value in the Format '…/…'?

I am currently working on a progress bar. Above or next to the progress bar I would like to display the value of progress made not as a percentage but rather in a format like this: 5/4 This shall ...
1
vote
1answer
18 views

Excel calculating percentage number occurs

I have a spreadsheet for a skills matrix. Individuals rank their skill knowledge from 1-4. I have been asked to write the percentage of employees that have chosen 3 or 4 for their knowledge on each ...
1
vote
1answer
40 views

Excel IF formula using before or after date and amount

I need help writing a formula for the following. superuser sample I hope you can see my image, and I explain myself well enough. I want column L (my new column) to display column I(dollar amount) ...
0
votes
1answer
24 views

Excel Formula to Calculate Sum of Time Duration Between a Specific Time Frame

I'm trying to calculate the total number of hours between two columns of start and end times but only those hours that fall between 9:00am and 5:00pm, Monday to Friday. For example, my data looks ...
0
votes
1answer
21 views

Need to extract multiple matches from a list of values

I am trying to extract data from a worksheet using an array formula but it will only return one (the first it finds) value. The formula I am using is: =INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ...
0
votes
2answers
459 views

How to Change Conditional Formatting of a Cell Based on a Change in the Cell Value

I am desperately looking for a way that allows me to conditionally format a cell depending on whether its value has increased, decreased or stayed the same. However, I don't want to compare its ...
0
votes
1answer
36 views

how to sum values in excel until they reach a specified total, then add the cell next in the column

I want to have a threshold value in one cell (A1) and take it as a reference for adding cells. Suppose I have A1 - 10 A2 - 4 A3 - 2 A4 - 3 A5 - 4 A6 - 6 I want to add cells based on A1(Threshold). ...
0
votes
1answer
30 views

How to configure IF function to dynamically display three data tables

I downloaded the Microsoft Office Excel Template called "Vehicle Log Book" and I'm having trouble adjusting the graph so it includes 3 items in the drop down list, instead of 2. I believe the issue ...