## New answers tagged worksheet-function

0
votes

### calculating the average of column that is not a number

You may also use Power Query.
Click Data > From File > From Excel Workbook > Browser your workbook > Tick "Select multiple items", select 2 tables > Click "Transform ...

0
votes

### calculating the average of column that is not a number

You could try using the following formula:
=LET(
a, XLOOKUP(C2:C7,E2:E5,F2:F5),
b, A2:A7,
c, UNIQUE(b),
HSTACK(c, MAP(c, LAMBDA(d, AVERAGE(FILTER(a,d=b))))))
Explanations:
Using ...

0
votes

### How to count cells that are within specified tolerance with Excel?

The general way to work with tolerances, is by saying that:
abs(Value_exact - Value_estimated) <= tolerance
This can easily be integrated in Excel's CountIf() function.

0
votes

Accepted

### Calculate duration between 2 milestones based on criteria

Try using the following formula:
• Formula used in cell B4
=LET(
_Seq, SEQUENCE(B2-B1+1,,B1),
ROWS(FILTER(_Seq, (1-ISNA(XMATCH(MONTH(_Seq),SEQUENCE(F2-F1+1,,F1)))))))
Or, Using TOCOL():
=...

1
vote

### How do I find common text values from multiple (>2) rows in Excel?

Came across this old question and figured if I found it, others might come looking for a similar solution. Anyway, for what it's worth, I think a good way to do this is either using BYROW to check ...

4
votes

Accepted

### Excel: Sum all quantities of unique items across all sheets

With Office 365 we can use TOCOL with a 3D reference. Then use either GROUPBY(Currently only available for Insiders(beta)) or UNIQUE,FILTER,and SUM.
=LET(
x,WRAPROWS(TOCOL(Sheet1:Sheet3!A2:B100,1),...

0
votes

### Excel: Sum all quantities of unique items across all sheets

This should be a comment but I'm not allowed to do that yet. There are a multitude of ways to solve this one.
A simple method is to use a grouping of SUMIFS to tally the total count of parts from ...

0
votes

### Excel: How to select a chart's data range using a corresponding value in another cell?

I am not clear on this. Posting a file may help more.
I am guessing that the names in the SummarySheet are the column header's names, right?
So then these would be used to select the second row ...

1
vote

### Nested If's with Vlookups not working in Excel

My original hope to populate information into 2 cells with a formula in a single cell was not able to be done in Excel without using VBA (not how I wanted to solve the problem as it would be overly ...

0
votes

### Excel Formula According to the attached table, the rank has been found according to Achieved %, in which some salesman does not have target

You could try using the following formula:
=LET(
_Data, VSTACK(A1:E1,SORTBY(A2:E15,E2:E15)),
IF(_Data=0,"",_Data))
And if you want to exclude the one which don't have a rank then ...

1
vote

### Creating a new column containing time in months based on specific conditions

Depending on your definition of a "month", perhaps:
=IFERROR(MIN(DATEDIF(B2,C2,"m"),60),60)

1
vote

### IF cell is BLANK then VLOOKUP multiple criteria

Try wrapping with an IF() function before performing all the Nested IFs() with VLOOKUP() functions:
=IF(C12="","",
IFERROR(IF(C12="Route Stage",VLOOKUP(D12,'Route Stages'...

0
votes

### Creating a new column containing time in months based on specific conditions

If you do not need an absolutely exact "month", then use e.g.
=ROUND( (C2-B2) / (365.25/12) ,0)
There is an average of 365.25 days in a year, and 12 months in the same year.

0
votes

### How can I make a volatile Excel formula static, specifically a formula containing the TODAY function?

Here is a non-macro answer:
Allow circular references by going to File>Options>Formulas check "Enable iterative calculation" (for Mac it is Excel menu>Preferences>Calculation ...

0
votes

### How can I combine two Excel formulas into one?

To combine two Excel formulas into one, you need to ensure that each condition is evaluated correctly and integrated logically. For example, if you have two COUNTIFS formulas that you want to combine ...

1
vote

Accepted

### Dynamic Array - How to Conditionally Generate Sums?

If efficiency is a concern, it's best to avoid iterative methods like REDUCE with INDEX and VSTACK, as they tend to perform very poorly when the number of iterations exceeds 1,000.
As an alternative, ...

0
votes

### Dynamic Array - How to Conditionally Generate Sums?

=LET(range,A2:F3,
REDUCE({"recieved_qty","po_number","po_item","stock_code","container_id","rec_process"},
SEQUENCE(ROWS(range)),
...

0
votes

### sum cells based on corresponding values in a data set in excel

Try using SUM() or SUMPRODUCT()
• Formula used in cell F2
=SUM((B2:E2="Yes")*(B$1:E$1=Itemtbl[Item])*Itemtbl[Assigned Number])
The above formula needs to copy down! Also consider using ...

1
vote

### How to sum values based on unique values in other column when filter is applied

Try using the following formula:
=SUM(DROP(UNIQUE(FILTER(HSTACK(A2:A14,C2:C14),MAP(D2:D14,LAMBDA(α,SUBTOTAL(103,α))))),,1))
When Manager Peter is selected:
When Country France is selected:

0
votes

### How do I lookup sheet names and create hyperlinks to sheets in a workbook?

So, here is way using Excel Macro 4.0 and with the help of dynamic functions, note that HYPERLINK() will not work with SPILL.
First, need to create a defined names to automatically grab the ...

0
votes

### Count working days between dates

You need to handle the cases separately. Try this (where C2 is the start date if present and D2 is the end date if present):
=IF(ISBLANK(C2), "", IF(ISBLANK(D2), NETWORKDAYS(C2, TODAY()), ...

1
vote

### Excel keyboard shortcut to edit formula in formula bar

You can use CTRL+F2 to do this!

Top 50 recent answers are included

#### Related Tags

worksheet-function × 8214microsoft-excel × 7324

microsoft-excel-2010 × 1637

microsoft-excel-2007 × 794

vba × 575

microsoft-excel-2016 × 443

microsoft-excel-2013 × 427

spreadsheet × 223

vlookup × 216

conditional-formatting × 185

google-spreadsheets × 161

macros × 142

libreoffice-calc × 116

date-time × 114

countif × 114

array × 102

pivot-table × 80

microsoft-excel-365 × 71

microsoft-office × 65

libreoffice × 60

date × 60

cells × 60

lookup × 58

charts × 57

openoffice-calc × 57