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

**2**

votes

**1**answer

42 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

**0**answers

30 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

**1**answer

40 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

**1**answer

37 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

**1**answer

16 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, MATCH(I8,[Test....

**0**

votes

**1**answer

24 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

**1**answer

34 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

**2**answers

90 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

**3**answers

34 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

**1**answer

46 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

**1**answer

57 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

**3**answers

52 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 1: ...

**0**

votes

**0**answers

38 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

**2**answers

37 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

**1**answer

43 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

**1**answer

31 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

**2**answers

29 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

**1**answer

39 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

**1**answer

21 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 aren'...

**0**

votes

**1**answer

31 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

**0**answers

14 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

**2**answers

28 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

**1**answer

56 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.
=IF(OR(ISNUMBER(SEARCH("6",M2)),ISNUMBER(SEARCH("2",M2)),ISNUMBER(SEARCH("a",M2)),...

**0**

votes

**1**answer

28 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

**1**answer

63 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

**2**answers

61 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

**3**answers

109 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

**1**answer

43 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

**1**answer

34 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

**1**answer

25 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

**1**answer

85 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) if ...

**0**

votes

**1**answer

43 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

**1**answer

40 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, ROW($...

**0**

votes

**2**answers

3k 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

**1**answer

121 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

**1**answer

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

**0**

votes

**1**answer

23 views

### Supplier Survey Excel formula

With my picture I'm trying to figure out what formula to use when adding the number of points that a different from one another (8) and (28) to add up to (36 total points).
Yet have a different ...

**1**

vote

**1**answer

70 views

### Excel how to apply a filter to a column with one value in it?

tl;dr How can we force Excel to lock-in a filter for a Column which only has 1 unique value within it?
I am having a very hard time coming up with a nice "label" for this problem, so I believe it is ...

**1**

vote

**1**answer

33 views

### Logic filtering of data in Excel between two columns

So here's a simple Excel question that has turned out to be more complicated than anticipated, so I'll just post it here.
I've logged some data where the input A shall be used to sort which rows are ...

**1**

vote

**0**answers

31 views

### Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...

**1**

vote

**1**answer

35 views

### How to Link Cells in Column to Cell in Rows

I have an Excel database. I want to link Column cells to Row Cells. One by one doing this will take so much time. I want to use some formula. Please help. Apart from that I also want to link cells to ...

**0**

votes

**0**answers

19 views

### How to visualize how close a value is to a given range?

Let's say I have the following ranges:
A - 0 to 10
B - 11 to 20
C - 21 to 30
And these values:
A1 = 1
A2 = 5
A3 = 9
Is there a way/formula to differentiate (e.g. sparkline, conditional ...

**0**

votes

**2**answers

32 views

### Subtracting hours

I'm working on a time tracking sheet. I have a weekly total column with the formula of =SUM(I4:I8). This entire column is formatted as [h]:mm. The current value is 31:49. I want to calculate how ...

**0**

votes

**1**answer

24 views

### Excel: How to check values in mutiple rows which mark values per day and highlight row with the earliest entry

I have a database wich shows value assigned to each customer in each week of the year. What I want is to have highlighted (i.e. marked with X, at the very end of my report) the earliest week on which ...

**0**

votes

**2**answers

45 views

### Converting hours over 24 to decimal

Given the values below, I want to convert the hours:minutes format to a decimal format.
8:30
33:30
If I use the formula =(O5-INT(O5))*24, which I found on multiple sites, the first value works ...

**0**

votes

**1**answer

29 views

### #NAME? on UDF that previously worked

I created a VBA function:
Function LOJA(x As String)
Dim a As Integer
Dim f As String
Dim f2 As String
Dim f3 As String
a = InStr(1, x, "-")
f = Right(x, Len(x) - a)
a = InStr(1, f, "-")
f2 = Right(f,...

**0**

votes

**0**answers

36 views

### Filter multi column list with repeating entries by partial string only with formula

Is it possible to filter an array by an partial string and list multiple entries only once using a single formula (without autofilter, VBA, or additional columns)
For example, I have the following ...

**0**

votes

**1**answer

20 views

### Excel: conditional formatting with with complex function AND

I have a sheet that looks like this:
So I have a list of documents to create, and I mark "1" on the column with the month in which I have created them.
Now I've been asked to put "1" also as a ...

**0**

votes

**2**answers

48 views

### How to take existing list (single column) and add rows between each entry

I have a list of 100 cities in column A and I'm trying to figure out how to take that list and duplicate each entry 3 additional times. I tried to use Offset to do this but was unsuccessful.
To ...

**1**

vote

**1**answer

93 views

### What is the Excel Formula to calculate based on consumption and rate

I need help to come out with a solution for this:
Actually I want to calculate my electricity consumption. as the attached pic. the first consumption 0-200kwh, rate $0.218, for 201-300 consumption ...