New answers tagged worksheet-function
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 ...
- 2,568
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)
- 8,921
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'...
- 2,568
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.
- 9,469
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 ...
- 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 ...
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, ...
- 351
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)),
...
- 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 ...
- 2,568
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:
- 2,568
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 ...
- 2,568
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()), ...
- 142
0
votes
Excel keyboard shortcut to edit formula in formula bar
You can use CTRL+F2 to do this!
0
votes
regex to find non-price consecutive digits not immediately after certain word for Apple Numbers
XYZ.*?(\d+)
Dot . Matches any character except line breaks.
* Quantifier. Match 0 or more of the preceding token.
? Lazy. Makes the preceding quantifier lazy, causing it to match as few characters as ...
- 27
0
votes
What is the Excel formula to filter these three lists?
A bit simpler solution for one list at a time (a formula for B9):
=LET(letters,$B$3:$N$3, data,INDEX($B$4:$N$6,MATCH(A9,$A$4:$A$6,0),),
FILTER(VSTACK(letters,data),(data>0)*(data<15)))
- 2,310
1
vote
How do I pull data into a new list separated by commas
You can use either:
=TEXTJOIN(", ",,FILTER(A6:D6,COUNTIF(A3:H3,A6:D6)))
or for entire rows:
=TEXTJOIN(", ",,FILTER(6:6,COUNTIF(3:3,6:6)))
- 2,310
2
votes
What is the Excel formula to filter these three lists?
Using Recursive REDUCE() function:
=LET(
_Data, A2:N4,
IFNA(DROP(REDUCE("",SEQUENCE(ROWS(_Data)),LAMBDA(a,b,
LET(c,CHOOSEROWS(_Data,b),d,DROP(c,,1),
VSTACK(a,IFNA(HSTACK(@c,...
- 2,568
2
votes
Accepted
Formula to return row pairs of data based on number in second row
If you want the range to include future values you can use the entire row:
=FILTER(1:2,(2:2>0)*(2:2<15))
If you want to skip a row you could use a few approaches such as CHOOSEROWS:
=FILTER(...
- 3,164
2
votes
Formula to return row pairs of data based on number in second row
Should be easy using the FILTER() function:
=FILTER(A1:M2,(A2:M2>0)*(A2:M2<=15))
Here is the update:
=CHOOSEROWS(FILTER(A1:M3,(A3:M3>0)*(A3:M3<=15),""),1,3)
- 2,568
1
vote
Formula to return employee codes from a lookup table of names and codes
Formula in B2
=VLOOKUP(DROP(TOCOL(A:A,1),1),D:E,2,0)
Explanation
VLOOKUP provides the basic functionality:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
The lookup_value is ...
- 3,164
0
votes
Median calculation of IFS function returns #VALUE! error
If the value in [@Market] is a text string, that is why you are getting the #VALUE! error.
The examples below hold true for both IF and IFS:
=IF(44, true, false) // non-zero numbers
=TRUE ...
- 3,164
1
vote
Is there simpler than OFFSET in Excel to get a cell range?
To answer your question if there's a shorter way of getting same result as =OFFSET(D3, 0, 0, A1, 2)
Other than removing the 0s I'd say no:
=OFFSET(D3,,,A1,2)
But I'd advise to use INDEX instead, since ...
- 389
1
vote
Accepted
Is there function that compares two ranges and returns the number of cells that changed values
=sumproduct(--(Range1 <> Range 2)) works
Range1 <> Range2 returns a bunch true/false values and the -- turns it into 0,1 values
- 180
1
vote
Is there simpler than OFFSET in Excel to get a cell range?
Select the range, type a name into the name box to the left of the formula bar, for e.g. myrangename. Now you can use
=myrangename
Range names can also be constructed with relative references, so they ...
- 23k
2
votes
How to easily take a column of data in excel and turn it into a list in which each value repeats several times before continuing to the next value
Using Office 365 you could approach it with TOCOL:
=TOCOL(IF({1,2,3,4},A1:A4))
Or
=TOCOL(IF(SEQUENCE(,4),A1:A4))
The horizontal array of integers ({1,1,1,1} would work just as well) converts to TRUE ...
- 389
-1
votes
How to easily take a column of data in excel and turn it into a list in which each value repeats several times before continuing to the next value
If it's a one time thing:
make a helper column, numbering each item 1,2,3... and so on
Copy both columns
Paste them four times, one below the other
Sort on the numbers
Delete the helper columns
...
- 5,536
1
vote
Accepted
Excel formula gives different result than manual calculation
All machines have a finite number of places for calculation and for display. This causes rounding errors.
A simple example: (1 / 3) x 3 logically equals exactly 1, but as a decimal calculation, 1 / 3 =...
- 31.2k
Top 50 recent answers are included