Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
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 ...