# Questions tagged [worksheet-function]

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

6,042
questions

**0**

votes

**1**answer

39 views

### Excel Formula to Compare Zip Codes on separate worksheets

I have an Excel worksheet containing a column with ZIP codes. On a second worksheet I have a column containing a huge list of ZIP codes and another column containing a list of sales territories ...

**0**

votes

**1**answer

20 views

### Calculating mean profit percentage with free product [closed]

I am trying to calculate the mean profit percentage on a mock-up company spreadsheet that sells goods. It includes the product id and invoice id along with some other information. I am trying to ...

**0**

votes

**0**answers

26 views

### How do I represent the change of a value in a single cell in another cell?

I am trying to track the change in value in a set of cells on a week to week basis. I have a large data set and a "dashboard" that auto-populates when I paste new data every week. I have a set of ...

**0**

votes

**0**answers

12 views

### Excel Forula returns “Refers to empty cells”

I am using this formula for a running balance:
=IF(AND(ISBLANK([@[EHEAP AMOUNT]]),ISBLANK([@CREDIT])),"",R1-[@[EHEAP AMOUNT]]+[@CREDIT])
EHEAP AMOUNT is the debit column and R is the balance column.
...

**0**

votes

**1**answer

26 views

### How can you create bold text from a formula in microsoft excel?

Im concatenating a number of text fields and want to make one of the filed bold in the final out put how can this be done?

**0**

votes

**0**answers

41 views

### Formula for filtering multiple criteria

Currently using Excel 2016 version form my desktop at work.
I came into a complicated issue of creating a formula to filter multiple criterias.
Filter certain values from 1 coloum, applying a ...

**0**

votes

**1**answer

29 views

### Excel “IFS” criteria values using cell references

My data consists of a month column A (1 through 12) and a values column B (financial returns) spanning multiple years. How do I count the number of month occurrences in the second half of the year ...

**1**

vote

**1**answer

45 views

### Excel “mmmm” formula in German

How does the mmmm function work in German? I tried to find it on YouTube no luck.
I want to display the month not with a number but the actual month name. In English version it is =text(Cell;"mmmm")
...

**0**

votes

**0**answers

12 views

### How to auto expand column with formula to bottom of sheet [migrated]

I am making a credit card debt paydown sheet that I want the user to be able to add more rows and have the formulas automatically fill down the column. I tried ARRAYFORMULA but because the results of ...

**0**

votes

**1**answer

73 views

### What Excel function to categorize, sort or lookup values within a cell

I am trying to create a workbook (non-VBA) for collating materials required for student experiments. At the moment, I have a list of all the experiments and their materials. Below is a excerpt of the ...

**0**

votes

**2**answers

46 views

### How to conditional format ONLY the first row that meets a condition?

I have an amortization table with a date next to each row. How do I highlight ONLY the first row that is a larger date than today's date?
Let's say the date column starts at A13. Obviously this...
=...

**0**

votes

**2**answers

34 views

### Get unique values from a comma separated values in a cell

In excel I have a column (actually multiple columns with ~30K rows) with each cell value looking something like this
7AA914BC, 898B70FB, 898B70FB, 15DD4C5B, 15DD4C5B, 98D2185E, 898BAC48, 98D2185E, ...

**0**

votes

**0**answers

23 views

### Lock cells in excel without disabling any other functions

I want to lock a cell or a row/column in excel and all other excel functions should be work. Normally when I protect a cell in excel with userinterfaceonly, I can expand and collapse group function ...

**1**

vote

**1**answer

18 views

### Custom data validation

Using the custom data validation rule, is it possible to only allow values that follow the below criteria:
Length = 7
First 3 digits = "ISO"
Digits 4,5 & 6 must be numbers
I currently allow any ...

**0**

votes

**0**answers

16 views

### Merging two tables with different KEY values

I want to merge two tables. There is a matching ID column however whilst majority of ID numbers are the same, Table A includes ID numbers that Table B doesn't and vice versa. For example: Table A ...

**0**

votes

**0**answers

11 views

### Using Apple Numbers for iOS, how can I get the daily average with some dates have muliple entries?

Date Company Earnings Hours *Earn/Hr Mls *Earn/Ml Runs
11/23 Quicky $102 4hr 0m $25.50 53 $1.92 6
11/23 Slowboys $20 1hr 0m $20.00 9 $2.22 1
11/22 Quicky ...

**1**

vote

**0**answers

37 views

### How do i edit the source list in microsoft excel data validation

I created a spreadsheet a few months ago and have a drop down list where i typed in a formula to populate the list from a range in another sheet. Now i have added more items to the range on the other ...

**1**

vote

**2**answers

64 views

### How to make multiple incremental lists based on contents of a column?

Spent two hours working on this problem and I'm ready for some guidance.
I have a column of names, there are 6 different names, and they could be attached to any number of rows. I have a text string ...

**0**

votes

**1**answer

38 views

### Grab Corresponding values from a range of cells

I have two tables in two different sheets that I need to use to combine info.
Sheet 1 - Table 1:
A | B | C
12-2-19 | Monday | Function Needed here
12-3-19 | Tuesday | ...

**0**

votes

**0**answers

51 views

### Replace a number for a list of numbers in a formula

How to add a range of numbers or list of numbers, "not number of cells" in a formula. For example: replace a "1" for "1 to 14", and replace a number "15" for "15 to 31".
This is the actual formula:
...

**1**

vote

**1**answer

26 views

### Asking Excel to Provide with the max value

Lets say there are two columns
VAR DATA
A 12
B 20
C 45
D 35
E 25
F 20
It is wanted that excel gives an output for VAR which has the highest value. So in the case ...

**0**

votes

**1**answer

64 views

### Extract unique values from multiple columns

I am trying to figure out how to extract the unique car numbers from 3 seperate sets of tables. The criteria is as follows...
Sheet A has 2 columns; Column A has a list of car numbers and Column B ...

**0**

votes

**1**answer

33 views

### Rows won't filter past the inserted table

I have a sheet, I had to cut off the data as of a certain date (the bottom total row) but then continue counting from that cut off date (another totals row added, new bottom totals row).
In order to ...

**0**

votes

**1**answer

74 views

### How to count distinct values in one column for each distinct value in another column in MS Excel?

Say I have the following data in an Excel sheet:
Country Fruit
USA Banana
USA Apple
USA Apple
USA Orange
USA Banana
USA Apple
UK Banana
UK Banana
UK Apple
I ...

**1**

vote

**1**answer

29 views

### Calculate Gregorian Julian Date from Regular Date in Excel

I am trying to get a Julian Date from a regular date (mm/dd/yyyy) in Excel. There seems to be two kinds of Julian date calculation.
One is described Here..
The other is somehow related to Gregorian ...

**-1**

votes

**1**answer

27 views

### How to change text color in excel using custom formatting in formula

I am combining few cells to make a report which needs to be sent my team daily. The cells has both positive and negative values and I need to show negative in RED color.
I tried below formula but it ...

**-1**

votes

**0**answers

24 views

### Excel formulas include external links becomes #REF

I created two excel files:
A. Data analysis sheet
B. Raw Data
A is full of formulas which are linked to B.
Now, I shared these two files with my colleague. When she turned on Enable Editing, all ...

**0**

votes

**1**answer

17 views

### Microsoft Excel - Formula to calculate age for a series of rows with different start and end dates?

I have a spreadsheet of individuals with a year every time contact was made with them. Each individual has a unique alphanumeric identifier. Each individual has a variable number of entries (rows) ...

**0**

votes

**0**answers

14 views

### Google sheets checkbox validation “If this then that” [migrated]

my sheet has two checkboxes who we will call "A" and "B". I need it so A must be checked in order for B to be checked. I cant find out how to do this using data validation but it seems there must be ...

**1**

vote

**1**answer

58 views

### Is there a way to tell Excel to count data per date from a list?

I'm going to try to make this as specific as possible, but I apologize in advance if I'm not clear! I don't do much logic coding in Excel.
I'm trying to write a formula that will basically count the ...

**-1**

votes

**1**answer

22 views

### How to hide formulas in excel 2016?

I am trying to hide the formula of a cell (from the function bar) if there are no values to calculate.
For example:
The function is in E3, using the values of C3 and D3. But if those two cells are ...

**1**

vote

**1**answer

23 views

### Auto updated weekly - Excel

I use a spreadsheet that has to be updated weekly and I want the date to change every week automatically. How do I set it up? THIS IS A SHARED FILE

**0**

votes

**2**answers

37 views

### Easiest method of displaying how many instances for each hour?

I have a call log which has thousands of calls in it. Each call is an entry in a CSV file which includes a datetime stamp.
What I would like to be able to do, is easily view how many calls there are ...

**0**

votes

**2**answers

41 views

### Changing font colour in a cell with a formula

I have a spreadsheet that is used by a group of people. Its purpose is for service admin to plan runs for service techs weekly.
They input each job and a travel & task estimated time in minutes ...

**0**

votes

**0**answers

34 views

### when cell value changes, copy the information to a new worksheet but also retain each change

Here's some code...
Private Sub Worksheet_Change(ByVal Target As Range)
If "B1" = "EURO" And Target.Address = "$B$2" Then
a = Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet4")...

**0**

votes

**0**answers

12 views

### Combining Formulas:

I need help combining these IF formula:
IF D7<>C$1 "OOP", "Current"
IF D7=C$1 and S7=0, T7>0, "No Rev"
IF D7=C$1 and S7<0, T7> 0, "Loss"
IF D7=C$1 and S7<0, R=0, "No Cost"
IF D7=C$1 ...

**1**

vote

**1**answer

37 views

### Formula to use as alternative date if cell is blank

I am trying to calculate employee service awards using a Date Joined column, and some staff have an alternative Date Joined due to a break in service.
Example
Date Joined=C17
Alternative Date ...

**0**

votes

**2**answers

90 views

### Complex Conditional Formatting

I'm a CPA with lots of Excel experience, but can't seem to figure out an answer to this question (of my own). Is there a way to apply conditional formatting for the below example:
EXAMPLE: I have a ...

**2**

votes

**1**answer

26 views

### If text value occurs more than 3 times in range, true / false

In the example below, I want to count all of the duplicates in the range A2:I2 and if there are more than three of the same occurrence, true else false I tried >3assuming it would show true if the ...

**0**

votes

**3**answers

48 views

### Excel - Indirect Formula

another question regarding Excel Indirect Formula.
I have columns E1:J202 filled with one of the following:
Green
Amber
Red
How can I use the indirect formula to count each of the above separately ...

**0**

votes

**1**answer

24 views

### Excel If Cell Dropdown is selected, then price in Cell 2 Dropdown is decreased by a certain amount

I have created a dropdown of percentages. The list is cells A1:A3 and the dropdown is cell B5.
I want the selection in B5 to change if the user selects a city in cell B6
Example:
User selects 0% in ...

**2**

votes

**0**answers

57 views

### I am unable to enter 6E029 in a cell in Excel

I am unable to enter the text from 6E000 to 6E999 in Excel.
Every time I enter this it is taking exponential form.
How can I format this to actual text?

**0**

votes

**1**answer

90 views

### Create unique value from now()?

In excel I am creating some unique customer ids, I thought the following formula would work, however it creates the same id's each day.
= "M" & INT(NOW()) + ROW()
Is there a way in excel to get ...

**0**

votes

**1**answer

23 views

### Leaving data blank if no condition is satisfied in Nested IF

I have a formula that will place a symbol, if my due date is less then today's date, and if my due date is more then todays date. I am trying to include leaving the cell blank, if neither applies but ...

**1**

vote

**1**answer

26 views

### Suspect behaviour getting cell width in Excel

From what I have gleaned, the way to get a cell's width in Excel is to enter the formula =CELL("width") into the cell in question. This works fine for one cell at a time, but when I copy the formula ...

**0**

votes

**1**answer

27 views

### Excel - Get uniques from a column after filtering

In my source data, I have one column with companies, and one with products. My goal is to, using the company name, populate a list of the unique products for that client.
In the image, there are ...

**0**

votes

**0**answers

22 views

### Calculating maximum of moving average values across varying periods in Excel

Is it possible to specify a row range to average in the calculation of max moving average? I would like to calculate the maximum diurnal (24h) value of 8h moving averages (from 5 pm previous to 5 pm ...

**1**

vote

**1**answer

41 views

### Most efficient way to calculate running totals in Excel

If you are keeping track of running totals such as the following:
A B C D E
-----------------------------------------
Type Value Total_AA Total_BB Total_CC
AA 1 1 ...

**0**

votes

**0**answers

40 views

### MS Excel - Separate texts by delimiter into specific columns

In MS Excel, I need to separate text by comma, extract three things from right and put them in three different columns i.e. city, State and Country.
Problem:
In the original text which is the full ...

**1**

vote

**1**answer

117 views

### How to reduce number of formulas and columns?

My goal is to adjust my pricing to match that of the competition, be it by reducing or increasing my current pricing. My model is a bit complicated and messy because their pricing includes tax, while ...