## New answers tagged worksheet-function

1

You can use an IF and an OR to make sure either WEEKLY or BILLS are populated:
=(IF(OR(B3>0,C3>0),D2+B3-C3,""))
This checked if B3 or C3 are over zero. If one of them are, it'll calculate, otherwise it'll show an empty string.

0

Application.Volatile is not enough.........the function will calculate every time the worksheet is calculates and that may not happen if you just change colors.

0

The equivalent to grep in Excel is AutoFilter. You can use it to view subsets of data based on criteria.
However, to use it your data would require re-organization. The criteria must appear in columns rather than rows. So you would a column for -6, -7, -8, -9, -10, etc.

1

The overriding issue is how to access the formula string of another cell from the worksheet. There is no existing worksheet function that can do this. So, you're going to have to do one of two things to start: 1) use find-and-replace to change the content of the cells so that they no longer hold formulas but hold strings instead, or 2) use VBA to access the ...

0

Use an array formula.
=AVERAGE(IF(B51:B57>60,60,B51:B57))
Enter with Ctrl+Shift+Enter.
This will replace all numbers in your array that are greater than 60 with 60 and then average them.

0

And here is a UDF that puts a TRUE / FALSE value in a helper cell.
The formula would look like =testReferences(G2,A:A,", ")
The following code gets pasted into VBA editor. It works for unlimited references.
Function testReferences(text As Range, list As Range, delimiter As String)
Dim arr() As String
Dim found As Boolean
If text = "" Then
...

1

Its not at all elegant, but this would work for up to 3 references.
You could combine with conditional formatting as required or put into a helper column.
=IF(IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=TRUE,1,
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=1,COUNTIF(A:A,G2),
...

0

Greetings: I wanted a formula to compute the second Wednesday of the current month and year to use to automatically add the date of the meeting to a sign in sheet for a monthly meeting. I used the great information in this post and created the formula below.
=DATE(YEAR(NOW()),MONTH(NOW()),1+7*2)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),8-4))
Thanks for the ...

3

If you haven't seen this link already, it could be of use. There are some formula examples down the bottom. http://stackoverflow.com/questions/10692653/excel-vba-to-create-every-possible-combination-of-a-range
However, the CROSSJOIN answer above is a much cleaner way.

8

You can use the CROSSJOIN function in Excel (PowerPivot add-in required for Excel 2010 & 2013 which you can download for free from Microsoft). Details about the CROSSJOIN function can be found on the MSDN website.
In sum, set up your base data as two (duplicate) tables table1 & table2 making sure they have different column names, then use ...

0

Is there a better solution?
Yes, there is.
Use tables (select your range and then: Insert - Table), and there you can write your formula like:
=SUMPRODUCT((YEAR(Table1[HeaderC])=2015)*(Table1[HeaderE]))
Of course, instead of HeaderC write the real header in column C, and the same for column E.

1

One other, slightly more readable, suggestion.
=SUM(IFERROR((YEAR(C:C)=2015)*E:E,0))
Use that as an array function (Press Ctrl + Shift + Enter, not just Enter)

0

Just after posting it I had an idea and it works:
Solution:
=SUMPRODUCT((YEAR(INDIRECT("C2:C"&(MATCH(0;E:E;-1));TRUE))=2015)*(INDIRECT("E2:E"&(MATCH(0;E:E;-1));TRUE)))
The match function finds the last cell with a value >= 0, for me that's working fine. Dependent on the use case, you would have to adjsut the match function...

0

Try
=SUMPRODUCT(($A$2:$E$10="o")*($A$3:$E$11="P"))
Adjust the ranges to suit your sheet. Do not use with whole column references, unless you like staring at the "Calculating ..." message. If the spreadsheet grows, consider using dynamic ranges.

0

You can construct the required array with a little manipulation, e.g.:
=SUMPRODUCT(COUNTIF(D:D,IF({0,1},">"&E1,"")))
though are you sure you really want to count all blanks within the entirety of column D?
Regards

2

That {1,2,2,3,3,3} example is in fact completely arbitrary and has nothing to do with the actual results. The COUNTIF() would actually return this {3,3,3,2,2,3,3,3,2,2}. That array is the result of COUNTIF() calculating for each element of it's second argument. Then, as the article says, each element of that array is used in division with 1 as the numerator. ...

0

I think Chris is correct though, if you want to sum all items that are shown on the worksheet after the filtering, you should be using the Subtotal function.
If you want to Sum the total of all the filtered out cells, you can use
=Sum(Q16:Q1532)-Subtotal(9,Q16:Q1532)

0

You can use the subtotal function. Looking at your question, I believe the formula would be =SUBTOTAL(9,Q1:Q1532). This will sum all filtered cells.

2

An approach with conditional formatting, without any VBA, involves the function FormulaText() which is available in Excel 2013 and up.
Create a conditional formula with a format that checks if the name of a specific sheet exists in the formula. For example, if a sheet is called "raw data", then this formula will highlight cells that refer to that sheet.
...

1

COUNTIFS() doesn't seem to like mixing array constants and cell references like that. It works fine if you hard-code the date like this:
=SUM(COUNTIF(D1:D4,{">12/1/2015",""}))
Or if you don't want to hard-code it, use this array formula. This formula must be confirmed with ctrl+shift+enter.
=SUM(--(D1:D4>E1),--(D1:D4=""))
Note that both of these ...

0

You need to use the IF function. The proper syntax is;
IF(logical_test, value_if_true, [value_if_false])
So, in B1 of SheetB use the following formula to check if A1 of both sheets are equal, then return the cell reference of A2. If it is not equal, it does nothing ("").
=IF(A1=SheetA!A1, SheetA!A2, "")

0

A Pivot Table like @EngineerToast has shown is a much simpler way to do this. But I do love a good ridiculously over-engineered solution, so here's one way to do it in VBA. I'm suspicious I did overcomplicate it with the Dictionary, probably could have done it just using some other quick count and then adding the already used letter to an array, but there we ...

0

For a one-time action, you can do this with a pivot table. If you need to do it often, this approach may get clumsy.
First, add column headings. I used Group and Name. select any cell in the list and click "Pivot Table" on the Insert ribbon (far left button).
It should automatically guess the range to which you want the pivot table applied. You should ...

0

When you select a range, the button to disable the error warning does appear as long as the cell you select to make the range does have a warning.

4

If you want an actual #N/A error in the cell then enter the formula
=IF(A1="No",NA(),"")
(where A1 is the cell with the drop-down), or if you just want the text "N/A" then
=IF(A1="No","N/A","")

0

#N/A means the items don't match. Pick a cell that you feel has a match in the ActiveData table. Use a formula along the lines of
=MySheet!A1='Active DS'!A1
Replace references with the two cells that you KNOW are a match. You'll find they are not. Clean the data.

0

Add a sheet to your workbook; let's call it "Setup".
In A1 put 8, in A2 put 9, in A3 put -5. Copy across as many columns as that formula with constants exists across columns in your original sheet. (Depending on how the range is organised, you may need the setup sheet to use A1, B1, C1 and copy down instead. The idea is to create a range that matches the ...

1

If you want to keep the last 7 characters, then just use:
=RIGHT(A1,7)

1

If your data is in cell A1 then your formula in B1 must be:
=RIGHT(A1,LEN(A1)-FIND("PK",A1,1)+1)
This formula works no matter how many characters you need to extract from the cell.
It will extract all the characters to the right of the first PK it finds including the PK string.

1

Good question, and such a useful thing to do. I found this solution a while back. Assuming your string is in cell A1, you can use this formula:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
Check out this SO thread for the deets: http://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string
I just tried this with ...

1

You could use Power Query to do that. Power Query is a free add-in from Microsoft for Excel 2010 and Excel 2013 and it is built into Excel 2016 on the Data ribbon as "Get & Transform".
Power Query has a command to split a text by delimiter and a setting to use the right-most delimiter.
Or, if you want a formula solution: calculate the length of the ...

0

Here's another way to handle decoding your times the way you want to enter them:
I left the start and end times as unformatted numbers to make the action more visible. In this example, your start time is 25 seconds after midnight or noon, which you would enter as 000025. As a number, it will be stored as 25, even though your formatting will make it look ...

4

One option would be to use a combination of cell formatting with the Excel TIME functions.
Format your time input cells (A2 and B2 in my example) as Text. The format expected will always be hhmmss, so enter the leading zero for times with single-digit hours. Then you can calculate with this formula:
=TIME(LEFT(B2,2), MID(B2,3,2), ...

1

Assuming that your data is sorted descending by column A, use in cell H4:
=IFERROR(INDEX($E$2:$E$17,MATCH(1,(($C$2:$C$17="success")*($B$2:$B$17=$G4)),0)),"error")
This is an array formula and must be confirmed with Ctrl - Shift - Enter.
Copy down.

0

I have a formula that should work for you that I've used in similar situations.
Use this formula in your non-Total sheets. It'll grab the sheet name, and look for that in your Total sheet, then return the column B (or whatever you put in the Index()) info. without repeating:
...

0

You need to change the quotes in "<TODAY()". Currently, this is treating the entire thing as a string and never evaluates TODAY(). The correct way to write this should be:
"<"&TODAY()
As the TODAY() is now outside of the quotes, it is treated as a formula rather than a string. It is then appended to "<", which, hopefully, should give you the ...

0

Separate the pay from Job Title, this will make your life easier and allow you to use excel formulas
enter =COUNTA(C2:N2)*A2 in the cell O2 and hit enter. this will count non-empty cells in the range C2 to N2 and multiply the number with A2
Copy the cell O2 to O3, O4 and so on. excel will automatically change the value of the formula to suit the row you're ...

0

I'm pretty sure this is the kind of result you want:
You pick a start and end date to produce a list of how many days for each month are within that range. The important thing here is that the values in C1:N1 are actual date values that have been formatted. This is much easier than using text values and then trying to calculate dates in your formula.
...

2

A few thing you would want to do first:
Separate Job Title and Job Amount into two columns.
Create and additional column to count the cells range where a date is
being input
Format appropriate cells for currency
This will allow you to use a formula to automatically calculate your answer. I am attaching pictures you can see.
Column O is a count formula ...

0

I used this formula in the end and it worked. I dont know why yours was still giving me an error.
Thank you for your help
=IF(B3="",AVERAGEIF(taille,A3,D7:D12),IF(A3="",AVERAGEIF(type,B3,D7:D12),IFERROR(AVERAGEIFS(D7:D12,taille,A3,type,B3),"No Combo")))

1

Bruce Wayne's solution is a good one. There is a simpler (but slightly less flexible approach) available also:
=SUMIF($B$1:$E$1,"="&B$7,$B2:$E2)
$B$1:$E$1 is the range the condition is being tested against. The row and columns are both anchored, because I am always testing against the same array of month names.
"="&B$7 is the condition. Here the ...

2

Assuming your data is laid out like with "Date" in A1 and "7" in E3, you can do:
=SUM(IF($A$2:$A$3=$A8,IF($B$1:$E$1=B$7,$B$2:$E$3)))
Be sure to enter with CTRL+SHIFT+ENTER

0

It's a very minor change.
=IF(G1="","",IF(G1=" LNN ","PENDING", "N/A"))
or
=IF(ISBLANK(G1),"",IF(G1=" LNN ","PENDING", "N/A"))
The IF statement is of the syntax IF(Test,True,False). If the condition is true, it returns whatever is in the True parameter. In any other case, it returns whatever is in the False parameter.

0

You need to use some IFs to make it working:
=IF(A3="",IF(B3="",AVERAGE(D7:D12),AVERAGEIF(D7:D12,type,B3)),IF(B3="",AVERAGEIF(D7:D12,taille,A3),AVERAGEIFS(D7:D12,taille,A3,type,B3)))

0

I'll focus on just the portion of your formula that you're asking about (the first IF test).
Partial text using a keyword
If the partial text is a unique word or string, you can look for its presence. Excel doesn't have a "CONTAINS()" function, but you can do the equivalent with some nesting.
=IF(ISERROR(SEARCH("mix",E6)),"","By Mix Design")
SEARCH ...

0

With sample data in A1, in B1 enter:
=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
and copy across:
EDIT#1:
If we start with material in columns A and B like this:
and run this short macro:
Sub dural()
Dim N As Long, i As Long, K As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
K = 1
For i = 2 To N
v = ...

0

I'm posting this wiki so that it will have an answer and can be closed. All information is straight from the comments made by @Jonno
=IF(ComparisonHere = 1, "OFFLINE", "ONLINE")
In context:
=IF(ImportXML("https://api.eveonline.com/corp/StarbaseList.xml.aspx?keyID=*******&vCode=***********","//row[@itemID='*******']/@state") = "1", "OFFLINE", ...

1

This isn't a worksheet function answer, but it doesn't use VBA either.
Have you tried using the "Text to columns" feature? In Excel 2010, it's one of the buttons on the Data ribbon.
Select your column.
Click the "Text to columns" button.
On the first page of the wizard, choose "Delimited", then Next.
On the second page, tick "Comma" to choose it as the ...

0

If you use Index/Match instead of Vlookup, you can keep the lookup table sorted descending like posted in your question. The formula is
=INDEX(B1:B10,MATCH(77,A1:A10,-1))
Match with -1 as the third parameter will return a value greater than or equal to the lookup value when the table is sorted descending.

0

After digging in to this for a long time, I learned that the lookup table must be sorted ascending. The documentation notes that it must be sorted, but did not specify the sort order. Anything besides ascending order results in the #N/A error.

Top 50 recent answers are included