New answers tagged worksheet-function
0
votes
Calculate monthly profit/loss from stock options positions
To calculate the monthly income earned from specific options strategies in Excel, you can use a combination of functions like SUMIFS, MONTH, and YEAR. Here's how you can set up the formula:
Create a ...
- 162
0
votes
Excel - result of =filter() in merged cells
Currently, I suggest you left the filter results (From A3 to A5), then try following formula in other column with no unmerged cells to check if you merge cells, will the result be correct.
=INDEX($A$3:...
- 3,266
0
votes
Accepted
Subtracting from today only if other cell is empty
Not sure what you're trying to do with the OR there... which is why it doesn't work.
You need a simple IF like this:
=IF(F11="","",IF(G11="",TODAY()-F11,G11-F11))
IF ...
- 7,454
1
vote
How can I do multiple IF conditions in MS EXCEL?
This is called a nested IF function. What you want to do is to at each step specify what to do if_true, and then if_false put another IF with the next condition.
Something like:
=IF(A1>75000,3000,...
- 7,454
-1
votes
How to use AND and VLOOKUP functions to assess multiple items without getting N/A or VALUE errors
=IF(A2="Secondary",IFERROR(IF(EXACT(VLOOKUP(B2,$G$3:$G$22,1,FALSE),B2),"Information"),"N/A"),"N/A")
2
votes
Accepted
Excel - return True/False value by searching multiple keywords in one column
I think the simplest way to do it would be to to add an OR to the first logic of your AND.
Like this:
=IF(AND(OR(D9="Matched",D9="Complete"),E9="Yes"),"Ready",&...
- 7,454
1
vote
Accepted
How to use AND and VLOOKUP functions to assess multiple items without getting N/A or VALUE errors
Your issue with the #N/A in C5, comes from the =VLOOKUP(B5,$G$3:$G$22,1,FALSE) (the 2nd part of the first AND) results in a #N/A throwing off the whole following function to stop working.
And the #...
- 7,454
0
votes
How to turn on and off data in a formula in excel
I came up with a solution on my own. I added an additional column to read 0 or 1. 1 if the inventory is needed 0 if not. Then in my space requirement column I added an IF statement (IF(A1=1,(calulate ...
1
vote
Accepted
Cell Reference based on Row
You need to do a SUMIF on the Used range, and subtract that from the Qty.
In the first Remaining cell (the one in the ORD1 row) you can enter (and drag the fill handle down to all the orders):
=C2-...
- 7,454
0
votes
Trying to Sum data within a selected time period (in decimal years data)
You can use:
=SUM( ( "BeginningDate:LastDate" >= "First date" ) * ( "BeginningDate:LastDate" <= "End date" ) * ( "Cum. Fire column" ) )
It ...
- 11
0
votes
Message Formatting with Excel
No, what you want is not directly possible with Excel. It simply does not do two things: 1) Support Java message format/ting as a formal, programmed in thing, and 2) Allow "inline" ...
- 11
1
vote
Why are my columns disappearing after I refresh the data?
You ARE in fact "losing" column S.
True, I'm sure, not part of the original query, but clearly now the query does actually produce a column of data that is placed overtop the current ...
- 11
0
votes
Accepted
How to dynamically return in each row the number of occurences of a string found in every column?
I'd like to share what I managed to come out with, which has to involve multiple formulas:
In A2, I manually define the starting cell of the searched area.
In B2, I automatically calculate the ...
- 818
0
votes
How to highlight all cells that has been sum up
Instead of conditional fortmatting, you could format the data as table without headers and add a totals row.
You could then color the totals row field to your liking and if you add more items to the ...
- 259
0
votes
How to highlight all cells that has been sum up
This will happen automatically if you click on A4 and then click inside
the formula :
- 444k
0
votes
Looking at Sales Data. How to determine if an order was a new order or a reorder for a specific customer and product?
You can use the following formula:
=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )
It uses the old school technique of simple "...
- 1
0
votes
Accepted
Copy cells next to each other to another work sheet
This is a classic situation where you can use a simple VLOOKUP().
I assume your name/number data is stored in the range C1:D21 on the sheet "CONTACT LIST" and in cell G1 on sheet "...
- 561
0
votes
COUNTIF using two conditions while ignoring duplicates
You can use COUNTIFS for this, as follows.
First create the column headers with the types and first row with the years (something like below, I put the type in the columns and the years in the rows ...
- 7,454
0
votes
COUNTIF using two conditions while ignoring duplicates
Under the assumption that you simply want to count everything for the different vehicle types, an easy solution would be the following. I stored the three columns of the data in the range A1:C22. The ...
- 561
1
vote
Excel Function to Concatenate & Exclude Zeros
You can extend the following formula to include as many items as you wish:
As per your tags, this assumes no Excel version constraints
=TEXTJOIN(
",",
TRUE,
BYCOL(
FILTER(...
- 8,020
2
votes
Accepted
Obtain leftmost cell and rightmost cell in a range that do not contain an error
With Office 365 we can use TAKE() and TOROW() to return the correct numbers.
TOROW() has the ability to ignore the errors and TAKE can take the first and last of what TOROW() returns:
=TAKE(TOROW(C3:...
- 22.4k
1
vote
How to paste only the formatted value not the actual value
Copy the cells, paste them in Word. Copy back from Word to Excel.
Just thought of this after a few minutes of Googling / not finding any solution...
- 111
1
vote
Equation for excel cell please
Assuming the idea here is to figuratively move the cursor from the left of the input n steps to the right (steps == 1st digit of your instruction), then n steps to the left (steps == 2nd digit of your ...
- 2,207
1
vote
Excel Function to Concatenate & Exclude Zeros
Formula
Only concatenate "Dog:"&B4 with "Cat:"&C4 , if B4 is not 0 * see note
=IF(B4<>0,"Dog:"&B4,"")&"Cat:"&C4
* ...
- 2,382
0
votes
Excel Function to Concatenate & Exclude Zeros
You want to use a conditional if statement in this concat string.
Syntax is:
IF(condition,value when true,value when false)
For what you are doing, if the value for "Cat" is in "C4&...
- 3,457
1
vote
How do I sum the lowest two values of two different column or range?
The difficulty with the first part is that you are finding two permanent 0's with SMALL (1,2) and finally one value of some kind (3).
The reason is that your values begin in D27 but the SMALL function ...
- 11
0
votes
Nested if conditions with Vlookup producing N/A instead of next conditional statement
Yes, it can be used within your construct.
Basically, your AND produces not either TRUE or FALSE, but rather TRUE or #ERROR!. That comes from your VLOOKUP's either producing a "result" or an ...
- 11
0
votes
Excel formula for calculating average of 52 cells out of possible 104 where blank cells exist
You can use the following formula to get the average of the last 52 non-blank cells in a range (the data is in Column A):
=IFERROR(AVERAGE(IF(ROW(A:A)>=LARGE(IF(A:A<>"",ROW(A:A)),...
- 7,454
0
votes
Nested if conditions with Vlookup producing N/A instead of next conditional statement
Use IFERROR(VLOOKUP(first set of args),VLOOKUP(second set of args)).
IFERROR() provides the value of the first argument, unless it is an error, in which case it provides the value of the second.
- 606
0
votes
General formula for multiplying numbers by themselves continuously, and divisibility of a column by a particular number
Enter 2 in e.g. A1, then =2*A1 in A2,
select cell A2 and use the "fill down" function to populate cells below A2.
Or more in the style you're suggesting =$A$1*A1 in A2, to multiply with A1-...
- 8,547
0
votes
Excel function determine if a cell is visible
It's an old task but the issue still appears today: how to detect hidden rows, especially in a bit more complicated situation.
Searching on the web shows a SUMPRODUCT construct, which is very slow in ...
0
votes
Accepted
Automatically cross-reference a word in a call
We're missing context (of the rest of the sheet and where this formula is going to be) but in principle it seems that what your asking is simply to have the cell reference instead of the word.
So ...
- 7,454
0
votes
Accepted
Auto insert words from one cell to another
You can use the UNIQUE function to get a list of company names from columns C, one per company name. But given that your column C is interrupted with the names of the day and the headers, you'll also ...
- 7,454
0
votes
how to filter "all rows where column x doesn't contain [0-9]"
Extremely similar to @Raysafarian's answer, but here is an alternative option just in case that is useful to anyone:
=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0}, A1))>0
It doesn't matter with numbers ...
- 21
0
votes
Formula to Combine 2 Different Logic Arguments
What you need is to have an OR and inside it the two logical scenarios (the first one with an AND.)
Your formula =IF(AND(A1=AUSTRALIA, B1=RED, B1=YELLOW, B1=BLUE),"YES", "NO") ...
- 7,454
0
votes
Is it possible to hide #Value in a long formula? I tried IFERROR but excel said there's too many arguments
The “You’ve entered too many arguments…” doesn't have to do with how long the formula is, it simply means you have put it in the wrong place (probably inside the IF but without nesting it.)
The ...
- 7,454
-1
votes
Is it possible to hide #Value in a long formula? I tried IFERROR but excel said there's too many arguments
If you want to hide #VALUE! in a lengthy formula, you can use the IFERROR function, but it may not work if there are too many arguments. One solution is to split the formula into smaller parts and ...
- 74
-1
votes
Find and sum all numbers in a cell
If you are working with higher version of Excel like, XL-2021 or 2022 or office 365 then you may try this,
=SUM(FILTERXML("<root><data>"&SUBSTITUTE(A1," ","<...
- 8,910
2
votes
Find and sum all numbers in a cell
One option:
=IF(A2="","",SUM(0+TEXTSPLIT(A2,TEXTSPLIT(A2,SEQUENCE(10,,0),,1),,1)))
- 491
0
votes
Excel / Sheets: How do I filter addresses based on a list of zip codes?
Basic Syntax
=FILTER(range,COUNTIF(lookupValues,zipCodeColumn))
Sample Formula
=FILTER(A:D,COUNTIF(G2:G5,D:D))
More Excel Reading
FILTER
COUNTIF
- 2,382
2
votes
Accepted
Microsoft Excel - Global formula to arrange multiple pattern text as desired
One option:
Formula in B1:
=LET(x,{"All De ","All Des ","Che Du ","Esp ","Imp De La ","R De La ","R De L'","R De ",&...
- 2,207
0
votes
Finding the first Non-Numeric Character in a String
Just out of interest I'll drop an answer to an (nearly 10 years) old question. I came across this while browsing and thought I'd add in an alternative based on new dynamic array functionality within ...
- 2,207
5
votes
Accepted
Finding the first Non-Numeric Character in a String but not excluding decimal denominators
I have four approaches:
Uses the CODE or CHAR functions
Uses the SUBSTITUTE function
Uses a recursive LAMBDA function
Appends & prepends digits
1. CODE / CHAR Approach
TRUE finds the ...
- 2,382
1
vote
is it possible to logical AND two values from two different columns in COUNTIFS?
With the latest version of your formula, the issue is at Transactions2023[Date] + Transactions2023[Time], DATEVALUE(Transactions2023[Date]) + TIMEVALUE(Transactions2023[Time]) <= 45027 + (17/24)
...
- 21.3k
1
vote
Accepted
Use COUNTIFS count values for specific range of the results
I don't think it's easily done, but here is one of doing it using an array formula:
=COUNTIFS(OFFSET(A$1,0,0,SMALL(IF("NBA"=$A:$A,ROW($A:$A)),100),1),"NBA",OFFSET(B$1,0,0,SMALL(IF(&...
- 7,454
0
votes
Excel VBA - Copy data from multiple sheets into a table
This is easy to achieve with a macro as follows:
Option Explicit
Sub copyrange()
Application.ScreenUpdating = False
Dim DestRng As Range, r As Long
For r = 2 To 7
Set DestRng = Sheet1....
- 1,030
Top 50 recent answers are included