Questions tagged [worksheet-function]

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

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

1
18 19
20
21 22
163