Questions tagged [worksheet-function]
Questions about using in-cell functions/formulas in spreadsheet software.
8,120
questions
1
vote
2
answers
674
views
Why can't INDIRECT() take a dynamic array reference just after opening a file?
I have a formula containing INDIRECT() pointing to some range -- call it B4:F8.
If I specify the range explicitly, like this:
=INDIRECT("B4:F8")
then it works fine.
However, if the range in ...
0
votes
0
answers
31
views
Does # in an excel formula move you to the last cell in the dataset?
I have a # in my formula and it showed up because I hit CTRL+DOWN when selecting to move to the last cell in the dataset.
Is that what it represents?
For example $A$1# would take you to the last ...
0
votes
2
answers
14k
views
Horizontal reference to vertical formula autofill - Microsoft Excel 2007
I'm trying to make a employee check-in chart for the year with one viewing tab, and 12 reference tabs for data entry. Within a given month, the dates are horizontal, D4:AG4, and the employees check in,...
0
votes
1
answer
98
views
How to use INDEX, MATCH and SMALL functions to populate multiple values with singular lookup value
I am creating a system that populates results from another sheet. As shown in the images below there are different "buckets". The buckets are various date ranges and there are different ...
1
vote
1
answer
295
views
=DATEVALUE() behaving different in Excel 365 online and Excel 365 local
I noticed that =DATEVALUE() behaves different in the online version of Excel 365 (i.e. it does not work) than in the local version of Excel 365 on my PC. Below are two screenshots of the two Excel ...
0
votes
1
answer
1k
views
Dynamic Spill-Down with INDIRECT
My goal is to create a table of the value of a single cell across many sheets as a spill-down array.
My current practice is to enter:
=INDIRECT("Sheet" & ROWS($1:1) & "!A1")
and manually ...
0
votes
2
answers
127
views
How to get "winner" based on row MAX
I'm trying to get the header value based on the highest value per row.
For example, given a sheet like:
A B C D
-------
1 2 3 4
3 3 1 2
I'd like to do reports on the winners of the row. For example ...
0
votes
2
answers
31
views
Convert duplicated columns to multiple entries in one column
Date,First,First,Second,Second
2022-01-01,one,two,three,four
2022-01-02,five,six,seven,eight
How can I convert a table like the above, with many of the same number of columns of the same title ('...
4
votes
4
answers
579
views
How to evaluate multiple OR'd conditions in COUNTIFS (or similar) row-by-row?
Consider the following sample data.
A
B
1
2
8
4
2
1
3
7
4
8
Separately, the count of A that is less than 5 is =COUNTIF(A1:A5, "<5") -> 4, and that for B is =COUNTIF(B1:B5, "...
1
vote
1
answer
98
views
Assign a value for specific words in certain rows, then add - seen 500 recommendations, but missing something. Please help asap
Let me start by saying yes, I searched lots of posts before making this one, BUT they either had me lost or wouldnt be exactly what I need. :( (And yes, its been many years since I've done this, so ...
0
votes
1
answer
32
views
How to append a unique number to an index that resets
I am creating an indexing id type system.
The way I am wanting it to work is the following:
A11 is showing "180 DAYS". A VLOOKUP is setup to search the key at Q3:R9. This generates an letter ...
-1
votes
1
answer
1k
views
Add until a certain value is reached and return the content of another cell
So I have the given table.
The formula will be in B6. The problem look like this: first I need to test if B2 is >= than B5. If it is I need to return B1 (Week 1), else add B2+C2 and test if the ...
0
votes
1
answer
140
views
I want VLOOKUP, but I want be able to see the "nth" unique result, not just the first
I want to create an Excel function in vba that works identically to VLOOKUP, but has an extra argument to let you pick the nth unique result.
Here's non working code that I've come up with so far. It ...
2
votes
2
answers
1k
views
How Can I make an excel column of Hyperlinks open in NON default browser
I have a ms excel 2007 worksheet with a column (A) of hyperlinks using the excel function in this way =HYPERLINK(A2,"url".
How Can I make (if this is possible) open all cels on that column in the NON ...
0
votes
1
answer
138
views
How do i create a Dynamic Dropdown Data Validation List based on pre selected criteria?
I seldom use Google Sheets or Excel, but now that I'm working on a card game, I have since thrusted myself into formulas and data validation.
I'm currently running into a logic issue.
I would like to ...
0
votes
0
answers
72
views
Take value if ID is the same
I need a formula to take a value if the ID is the same. Example image:
I have table with ID and Value.
In first row is 22222 and 15. If I change 15 (in first row) to 55, then the value in table for ...
0
votes
2
answers
712
views
count only first occurrence
I wonder if someone could help as I have been stumped on this for some time.
I have a excel spreadsheet with multiple pages.
On the "master sheet" i have several columns....
A is week ...
0
votes
0
answers
61
views
Why does Microsoft recommend not to reference entire columns in Excel formulas?
I am trying to save a workbook with a large number of COUNTIF() formulas referencing another workbook, which refer to entire columns. Saving this workbook takes much longer than calculating the values ...
0
votes
1
answer
78
views
Excel Formula to find the price based on industry and number of transactions
I am trying to determine the price for my input table which has the specific industry type and the average monthly transactions based on the reference table 1 which has the price for different ...
0
votes
1
answer
653
views
Remove formula in cell using vba
I have made two vba codes.
For doing round off on selected cell. Working fine
For undoing round off on selected cells. This has a little issue. After i do this it make the cells isformula true. But ...
0
votes
0
answers
375
views
Counting rows with missing data
I would like to count the number of rows with data missing in any of multiple specified columns. I am currently counting missing data in each column using the formula
=COUNTIFS($F7:$F96, "1",...
0
votes
2
answers
333
views
Fast array formula for running total of a column for consecutive identical values in another column
This is a follow-up question to Excel array formula for counting consecutive identical values. Here the problem is slightly more complicated.
I have a column (column E) with integer values, and ...
0
votes
1
answer
170
views
Excel table formula where column name is taken from a different column
I'm trying to figure out if and how the following is possible:
Imagine a Excel ListObject table with five columns
first three are named Option 1, Option 2, Option 3 and contain arbitrary values
...
0
votes
2
answers
112
views
Are matrix and array formulae in Excel multithreaded?
I know that Excel uses multithreading for calculation, but I could not find details about multithreading for matrix and array formulas, such as MMULT, MAP, REDUCE and the like.
I have two questions:
...
0
votes
0
answers
173
views
Incorrect sum when "+" to add 2 cell values
I have a weird problem. When I try to add up two cell values using =C79+C80, I don't get the correct sum but with =sum(C79:C80), the result is displayed correctly.
The cells C79 and C80hold formulas ...
-2
votes
2
answers
48
views
How to use Address function to write in the found cell? Microsoft Excel
I am trying to find a specific cell based on variable data. Currently, I'm using the "address" function to give the name of the cell, in this case Q10, and then filling it manually. Is there ...
1
vote
2
answers
50
views
How do I modify this Excel formula?
I have this issue: I am using Vlookup to retrieve data from multiple cells with multiple Vlookup ex.:
Vlookup (A1, A3:K20, 2, 0)&","& Vlookup (A1, A3:K20, 3, 0)&","&Vlookup (A1, A3:K20,...
0
votes
1
answer
695
views
Excel: calculating "weighted ranking"
I have the following data set with a list of players and their contribution to the group they belong.
I've calculated the % of the player contribution within his/her group in "% Player ...
1
vote
1
answer
42
views
How to create a consecutive between 2 number in excel
For example, I have 33 in cell A1, and 40 in cell B1, I want a formula in cell C1, to write an arithmetic sequence from 33 to 40 like this: 33, 34, 35, 36, 37, 38, 39, 40
1
vote
3
answers
221
views
IF statements excel help referring to multiple cells
I am struggling a bit with my IF statement and was wondering if I can get some help. I made a new column and want to have it filled based on three other cells. For example, you can see the attachment ...
7
votes
3
answers
69k
views
Filter for first unique instance a column in Excel?
How can I filter for unique values in a particular column (disregarding the values in the other columns for that row, and so the first instance of a new value would be fine)?
So, for example:
1 ...
0
votes
0
answers
39
views
How to add a cell automatically to a sheet when I add another cell on another sheet?
I have some time series data, so I will add one new data point to one excel sheet for several sheets in an excel file, then I also need to add all these new data point to another sheet, so that I can ...
0
votes
1
answer
686
views
How to Create a Spilling Array that Takes a Rolling Average
Reversing the order of the dates works, but I'm more interested in knowing how to do it in this specific way. I'm trying to take a rolling average, ideally it would be nice to change the depth from a ...
0
votes
0
answers
49
views
Formulae or logic to find and combine values from two different tables
I am a newbie to excel and I need your help in understanding how I can find a solution to this problem.
Problem:
I have two tables.
Table 1:
Table 1:
The top row shows the machinery names, 2nd row ...
0
votes
1
answer
138
views
Cumulative total errors
Hello I have formula that is indeed working, but only when I Select File > Options > Enable Interative Calculation.
I have a column that has percentages
In the column to the right (Column H) I ...
0
votes
6
answers
9k
views
Refer to column with specific Header Name
I export an excel file (source) every day and I need to link specific columns from this export file to another excel file (reporting). But columns in source file may varied, thus I can not use classic ...
0
votes
1
answer
3k
views
Excel 2007 - find max value based on status criteria in data list
I need to find the largest value (aging days) in a set of data based on the item status. Much like a SUMIF function, but a MAXIF (if one existed). My data is not sorted by status. I basically want ...
1
vote
0
answers
106
views
Formula for data distribution
I would like to know if there is a way to automatically distribute the amounts from column H into column D in 3 sections (Blue, Green, and Yellow) starting from D28 (Start) and climbing to the top D3 (...
0
votes
1
answer
700
views
Compass bearings on excel
Would like some help creating an excel formula:
Compass bearings are from 0 to 359. I have attached a picture of how it looks like here:
Basically, I am looking for a solution for when I input 2 ...
0
votes
2
answers
60
views
Lookup a specific rowvalue when the column depends on another cell (row/column combination)
I have an excel sheet with a very large, scarcely populated matrix.
The first column describes an item, and the columns after that whether an item belongs to a certain group. Each item can only belong ...
0
votes
3
answers
76
views
Expected value from one cell
I’m trying to calculate the expected value from a set of numbers. All the numbers are in 1 cell separated by commas and I can’t split them into different cells. Is there any way to calculate this from ...
2
votes
2
answers
2k
views
Copy Excel formula itself to clipboard
There are many questions about copying values instead of formulas, but what I am seeking is exactly the opposite. I want copy the formula itself, not the result of its calculation from some cell, and ...
1
vote
0
answers
12
views
How can I generate a multi column sorted list in Excel based on data pasted into another sheet?
Okay, so I can't unfortunately go into too much detail due to the sensitive nature of the data, but the short of it is that I've got a table I paste into "Source" that I'd like to have two ...
1
vote
2
answers
427
views
Excel / LibreOffice Calc: find item in column with latest first occurrence?
In a Calc or Excel sheet I have a column with bunch of names. Each name can occur one or multiple times.
Each name has a 'first occurrence' i.e. the first row in which it appears.
I'm looking for an ...
16
votes
10
answers
114k
views
Is there a function in Excel to find the maximum absolute value of a range?
I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= MAX(ABS(MIN(A1:A10)),ABS(MAX(...
0
votes
1
answer
44
views
Calc financial calculations
I've got a spreadsheet set up to record household financial transactions. One of the features I'd like to implement is a 'who owes what to whom' indicator. Simple enough with only two in the household,...
1
vote
4
answers
16k
views
Excel: What can cause manual calculation / F9 to stop recalculating?
What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?
I've Googled this problem and ruled out all the explanations I can find:
Calculation settings are set ...
0
votes
1
answer
1k
views
Calculate and display Best Seller?
I'm wondering if anybody knows a solution before i start trying and failing for a few hours...
I have a table of all my orders since March, which includes the value of the orders, the date the order ...
0
votes
1
answer
310
views
Autofill Blanks with Zeros if no other Numeral Present
I have a working system for giving employees an opportunity to track their goal progress on a week to week/monthly basis. I've now protected all cells except the 'Monthly Admission Goal' and grid ...
0
votes
3
answers
382
views
Microsoft excel - create 2D table from list of pairs
I'd like to ask for help with following excel issue: I am interpreting results of a questionnaire so I have a pair of values. First column contains a category, let's say A,B,C, while the second has a ...