## Hot answers tagged worksheet-function

10

This sounds like a straightforward AND condition.
=IF(AND(R1,S1),"Yes","No")

3

If you mean TRUE/FALSE instead of "TRUE"/"FALSE" (the former set being Boolean values, the latter being strings), I personally prefer the former, and would keep any outputs to a Boolean format as well wherever possible. It's useful to keep these as Boolean, because it makes writing formulas based on them (and returning them) a lot easier. I'll give solutions ...

3

I'd suggest using a third column to mark when an item is completed. Then you can use conditional formatting to handle the strike-through for you, and adjust your sum formulas to automatically account for the completed items. Also, things get a bit easier in the long run if you move your sums to a different column. See the example below.
Formulas:
F1 ...

3

Two options:
=concatenate(expression1, expression2, ...)
or
=expression1 & expression2 & ...
The result will be the same from either of them.
If you wish to format any of the expressions then have a look into TEXT(expression,format)
which allows the same as Custom inside Format cell (CTRL+1).
NOTE: as always the , between expressions may be ; or ...

2

NOTE HERE: I'm just viewing your IF() and changing the condition-statement.
If this does not work for you - it might at least give an idea of how to write/change it.
=IF(
AND(INDIRECT("LargerSheet!$B"& //Cell starting with "$B" and ending with
MATCH($A1,LargerSheet!$A:$A,1) //row of the last date for the name.
)<$B1, ...

2

I had to back-track your series of questions to see where the problem is, but (with only minor modifications) @Hannu's formula is pretty much right. The problem you're having with implementing it is that your spreadsheet layout does not match what was specified by @jbmorch in their answer to your previous question.
The layout was very clearly specified in ...

2

I'm going to use your basic formula and change it to work as an array function.
The function can go on one line I just seperated the if statement to draw attention to it
=VLOOKUP((SMALL(IF(
(Data!Y:Y="3")*(Data!H:H=3)*((Data!V:V="4")+(Data!V:V="5")),
Data!A:A),1)),Data!A:B,2,0)
In an if statement 1 is treated as TRUE and 0 is treated as FALSE so a * ...

2

If you have two variables that are normally distributed and assumed to be independent, then the joint density function for those two variables is just
J(x,y) = P(x)*P(y)
where P(x) is the probability density function of X at x, and P(y) is the probability density function of Y at y.
So, in Excel, if you want to find J(x,y), the value of the joint density ...

1

I'm going to put the randomization in the formula itself. You could always put this in another column if you want.
Column A is name
Column B is office
Column C is year
Cell G2 is the office you are interested in
Cell G3 is the year you are interested in
=INDIRECT("A"&LARGE(IF($B$2:$B$7=G2,IF($C$2:$C$7=G3,ROW($A$2:$A$7),0),0), ...

1

Your question isn't clear if you actually have hyper links or not (some are coloured, some are underlined and some not)
I have no idea if this can be done with worksheet functions, but this VBa does it.
Option Explicit
Sub CrazyAirlines()
'************** There are things you may need to edit here
Dim currentRow As Integer
currentRow = 1 'I assume we ...

1

So after Tyson got me thinking about Paste Special, I found out that you can do this in VBA;
Sub testexport()
'
' export Macro
Range("B20:AA45").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.SaveAs Filename:= _
"C:\!LOCAL_STORE\Book2.csv" _
, ...

1

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.
http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872
I would start a Query based on your Application table. I would add an Index column (row number).
Then I ...

1

Excel formulas handle the special error values of #N/A and #DIV/0 differently than other (real) values. Depending on the formulas you use, they get returned as the result no matter where they show up. So, your HLOOKUP is returning #N/A when it can't find the value, and even though you are trying to skip evaluating the HLOOKUP in the IF statement and return ...

1

Pretty straightforward transformation of
If A is true and B is false, return "Pass" If B is true and A is false, return "Fail" If both A and B are false, return "" If both A and B are true, return ""
I assume the values in Column A and Column B are only Booleans(true/false)
=IF(AND(A2,NOT(B2)),"Pass",IF(AND(NOT(A2),B2),"Fail",""))

1

Assuming that the values in A and B are only Booleans,
=IF(A1=B1, "", IF(A1,"Pass","Fail"))
is all you need.

1

assuming your array is running off of a match of column values, I would suggest you just reverse the column values so values in column 5 of the array would now be in column 1, and then use min() instead. Getting the first occurrence is always easier than the last.

1

Go to where you want to keep your dropdown list (e.g. cell A1) Create a drop down box with data validation (Data > Data Validation > Data Validation), create a list of the names of your 8 named ranges
Then you can replace Financials with INDIRECT(A1) in your formula:
=SUMPRODUCT(SUMIF(INDIRECT(INDIRECT(A1)&"!$B3:$B10000"),'Information ...

1

You can check if a cell has been struck through by creating your own formula. This can be achieved by using the Developer tools and creating a module.
Press Alt + F11
Insert Module
Paste in the following code
Function strikethrough(r As Range)
strikethrough = r.Font.Strikethrough
End Function
Return to the spreadsheet and you can use ...

1

Edit based on Iszi's comment:
end_time - start_time + TIME(0,1,0)
Documentation for the TIME() function
Original answer:
You can add 1 minute in your end_time - start_time calculation by realizing that in Excel 1 day is equal to the integer 1, so your formula would be something like:
end_time - start_time + 1/24/60
(Dividing by 24 hours and 60 ...

1

You could do it without math. For start time in A1 and end time in B2:
=SUBSTITUTE(SUBSTITUTE(TEXT(B1-A1,"h:mm"),":29",":30"),":59",":00")
SUBSTITUTE is a fairly simple text substitution function. It requires three parameters:
Original text
String to be replaced.
Replacement string.
SUBSTITUTE only replaces text when there is a match to parameter 2, ...

1

You can use the OFFSET function
OFFSET(reference, rows, cols, [height], [width])
Reference Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows Required. The number of rows, up or down, that you want the upper-left cell to ...

1

As long as you want only these fairly simple calculations,
you can use the AVERAGEIFS, COUNTIFS, and SUMIFS functions.
COUNTIFS simply counts the cells in a range that meet specified criteria.
For example,
=COUNTIFS(A2:A5, "foo")
returns the number of occurrences of foo in column A.
(This can be expanded to test multiple conditions, including <, ...

1

What I want to do is to copy one column to another worksheet but not as a 'copy&paste'. I want to be able to update the first worksheet so that the other one updates with the changes e.g. if I changed 'Company 1@ to 'Company 234' it would change on both worksheets
Oh, I see! You are speaking of so-called "link" aren't you?
Try this simple formula ...

1

I guess you have defined H26 to be =H24-H25,
and you are looking for a definition of H25 that is one of two formulas based on E25 or F25,
depending on which one has data. The answer is to use the IF() function. The general usage is
=IF(true_or_false, value_if_first_arg_is_true, value_if_first_arg_is_false)
You might use it like this:
=IF(ISBLANK(E25), ...

1

I don't doubt the truth of this sentence but I struggle to follow the logic: "This is mission-critical software, and its way too complex for any sane person to try and touch it.".
Anyway ...
I would try to separate this into two Excel files. One would gather the data using the VBA plugin, and would only be updated on machines with "terminal access". ...

1

I would use an if statement to check if the cells are filled
=IF(COUNT(B2:B3)=2,B3-B2,"")
If you then average the row the blank cells will get ignored and you should get the value you want.
For your table below. Average change would be
=SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""),(A3:C3-A2:C2))/SUMPRODUCT(--(A2:C2<>""),--(A3:C3<>""))
...

1

If you want a VBA solution, copy this into a module.
Function Score(R As Range, Col As String)
Dim ThisCell As Range
Dim Dif As Integer
Dim Cnt As Integer
Dim PosMove As Integer
Dim NegMove As Integer
Dim PrevNum As Integer
Dim ThisNum As Integer
PrevNum = 9999
For Each ThisCell In R.Cells
If ...

1

Perhaps I’m misunderstanding the question,
but this seems like it can be done with very simple function(s).
To find the row in Column B where the value in C1 appears, use
=MATCH(C1,B$1:B$1000000,0)
If cell A1 contains 1, A2 contains 2, etc, then you’re done.
But, if you need to actually retrieve the value from A642, use
=OFFSET(A$1, (the above)-1, 0)
...

1

This does exactly what you want.
Option Explicit
Sub DoTheThing()
Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW
Do While (Range("A" & row).Value <> "")
Dim vals() As String
vals = Split(Range("B" & row).Value, ",")
Dim lookUpValue As String
lookUpValue = Range("A" & row).Value
Dim result As String
...

1

The way I would go about it would be to start by breaking up the problem into a bunch of columns, each with a piece of the problem. For example:
A B C D E F G H I J K L M N
1 12 0,12,13,14 2 5 8 0 12 13 14 Yes Yes No No Yes, Yes, No, No
2 101 101,102,103,104 4 8 12 101 102 103 ...

Only top voted, non community-wiki answers of a minimum length are eligible