Skip to main content

New answers tagged

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 ...
Emily's user avatar
  • 3,692
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 ...
Mayukh Bhattacharya's user avatar
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.
Dominique's user avatar
  • 2,285
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(): =...
Mayukh Bhattacharya's user avatar
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 ...
RnDMonkey's user avatar
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),...
Scott Craner's user avatar
  • 23.4k
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 ...
Nate Z's user avatar
  • 21
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 ...
boobounder's user avatar
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 ...
cgru's user avatar
  • 21
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 ...
Mayukh Bhattacharya's user avatar
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)
Ron Rosenfeld's user avatar
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'...
Mayukh Bhattacharya's user avatar
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.
Hannu's user avatar
  • 9,494
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 ...
iamseb's user avatar
  • 1
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 ...
BBAADD GAMERS's user avatar
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, ...
DjC's user avatar
  • 361
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)), ...
P.b's user avatar
  • 389
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 ...
Mayukh Bhattacharya's user avatar
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:
Mayukh Bhattacharya's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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()), ...
Neil T's user avatar
  • 142
1 vote

Excel keyboard shortcut to edit formula in formula bar

You can use CTRL+F2 to do this!
Wanderlei Hüttel's user avatar

Top 50 recent answers are included