Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
6,042
questions
0
votes
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
2answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
0answers
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
0answers
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
1answer
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
2answers
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
1answer
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
3answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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 ...
