Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
6,046
questions
0
votes
0answers
13 views
Excel 2016 - Calculating Each Hour Worked by Multiple Employees to get the total Cost of Each Hour
I currently have multiple instances of tables set up to accomplish this, but the base table headers that I currently have:
Employee ID - Entered
Hourly Pay Rate - Entered (but on another table, so ...
0
votes
1answer
28 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
18 views
Calculating mean profit percentage with free product
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
22 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
20 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
28 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 ...
-1
votes
0answers
27 views
Excel Formula to calculate the declining Sales over few months [closed]
I need assistance on Excel formulas for a following situation.
I have around 900 products and sales of those products for the past 11 months. I am trying to find out which are the products slowing ...
0
votes
1answer
70 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
31 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
63 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
28 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
31 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
21 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 ...
