## Hot answers tagged worksheet-function

66

The caret signifies a power operation. The curly bracketed expression, {1,2,3}, is an array containing the values 1, 2, and 3.
As noted by @JasonClement, this formula only returns a value when entered as an array formula with Ctrl+Shift+Enter. As an array formula, the result is a 4-by-3 matrix containing the values below.
A1^1 A1^2 A1^3
A2^1 A2^2 A2^3
...

9

The caret operator is for specifying an exponent. For instance, 2^4 will return 2 to the 4th power (2*2*2*2), which is 16.
Curly brackets like this are a way of defining an array, which could be used wherever a range could also be specified. For instance, using the INDEX formula with an array like so: =INDEX({2,5,7,9}, 4) will return the 4th item in the ...

5

You are correct, Excel 2013 has the FORMULATEXT function which shows the formula as text.
Since Excel 2010 does not have this feature, there are a couple ways you can show formulas without VBA;
By pressing Ctrl + ` (that's the tilde key
above the Tab). This one shows all formulas in the sheet.
You can place a space it the cell before the =. This causes ...

2

Some functions will accept a discontiguous range as an argument, e.g. FREQUENCY, RANK, SMALL, LARGE.
In this case you could use one of those latter two, e.g.
=SMALL(MyPicks,RANDBETWEEN(1,9))
If the size of MyPicks might be variable, or some cells may not be populated you could make the RANDBETWEEN part depend on the number of values in MyPicks, i.e.
...

2

You can determine greater value using MAX(A1,B1) function :
IF(MAX(A1,B1)=0,0,IF(AND(MAX(A1,B1)>0,MAX(A1,B1)<=10),1,IF(AND(MAX(A1,B1)>10,MAX(A1,B1)<=80),2,IF(MAX(A1,B1)>80,3))))
If all values are positive number, you don't have to check lower bounds so you can simplify the function this way:
...

2

I think this is what you're trying to do, if I understood your question correctly. In C1:
=IF(OR(ISBLANK(A1),ISBLANK(A2),ISBLANK(A3),ISBLANK(A5),ISBLANK(A6),ISBLANK(A8)),"","This is a message")

2

How about this little beauty:
=SUM(FREQUENCY(MyPicks,MyPicks))
Therefore, your whole formula would be:
=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

1

I think what you want is some sort of tiered data-validation, like -
click for larger
So in the Ribbon you'll have data - data validation. You can set lists that are available based on conditions. For instance, D2 is restricted to the list in A6:A8 by using "List" with that as the sort.
Now, it's a little bit trickier for E2, but basically it picks ...

1

This is similar to Gary’s Student’s approach. Define the following VBA function:
Function MyRowHidden(ref As Range)
MyRowHidden = Rows(ref.Row).Hidden
End Function
See How do I add VBA in MS Office?
if you need help with that. Now you can use MyRowHidden(cell)
to check whether the row containing cell is hidden.
The way I devised to solve the ...

1

You can use the SUMIF function for this. You'll want something like:
=SUMIF(A5:A47, "<>Inventory*", L5:L47)

1

Begin by removing all the unnecessary loops. Replace:
For j = 41 To 60
ThisWorkbook.Worksheets("Breakdown").Cells(j, "A") = ""
ThisWorkbook.Worksheets("Breakdown").Cells(j, "D") = ""
ThisWorkbook.Worksheets("Breakdown").Cells(j, "E") = ""
ThisWorkbook.Worksheets("Breakdown").Cells(j, "F") = ""
...

1

I changed the part you needed via introducing a boolean variable bFlag.
Private Sub FloorWallTileCombo_Click()
Dim TileSearch As String
Dim TotalPrice As Double, TotalSF As Double, TotalSurCap As Double, TotalCorCap As Double
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Breakdown")
Dim bFlag As Boolean
ws.Range("A41:A60").Value2 = vbNullString
...

1

As mentioned elsewhere, you should be using commas, not semicolons.
In addition, because you only have two possible return values, I would recommend using the OR function instead of nesting your IF statements:
=IF(OR(E3=E2, F3=E2, E3=F2, F3=F2), "No", "Yes")

1

Here's a formula that works for your example no matter what your locale settings are:
=TEXT(DATE(MID(A1,7,2),LEFT(A1,2),MID(A1,4,2)),"dd-mmm-yy") & " to " & TEXT(DATE(RIGHT(A1,2),MID(A1,13,2),MID(A1,16,2)),"dd-mmm-yy")
Here's one that works no matter what's in between the dates so long as the dates are of the format mm/dd/yy and at the very ...

1

I think you want:
='Live Inventory Sheet'!D3
Put this in the Pineapple sheet on cell D2.
Do the same for each item in your list.

1

If nothing will be in the cells prior to you entering data:
=COUNTIF(C5:C40,"")

1

So it seems as though the main reason for the forumlae not being copied down is as stated above; the table was not auto-expanding when new rows were added unless they were created by dragging down and duplicating the rows above.
The formulae being copied down were variations on the same VLOOKUP, and the lookup value used in these formulae was in the column ...

1

Assuming your table starts in cell A1, I think this formula should do it:
=IF(IFERROR(MATCH($B1;$A$1:$A$5;0)>0;"")="";"";$C1)
The MATCH-statement searches for the value in B1 in the A1:A5 range, and returns a #N/A-error if it can't find the value (the third argument, 0, means we want an exact match).
Then the IFERROR-statement returns an empty string, ...

1

Since you only need values found in both columns, you only need to search one column for each value found in the other column. To do this, you could use the following formula in cell C1:
=IF(ISERROR(MATCH(B1, A:A, 0)), "", B1)
Then copy-paste C1 down until the end of column B. This would display the value of the cell in column B if the same value is ...

1

Either just use the worksheet-formula in your macro as well, i.e.
Function GetLastValueInColumn(r As Range) As Range
GetLastValueInColumn = Application.WorksheetFunction.LOOKUP(2,1/(r<>""),r)
End Function
Or use some VBA specific methods, a couple are detailed in this answer on StackOverflow.

1

Your question is very unclear, but let's assume the data looks like this
A B C D
Adam 21 Bill
Bill 22 Dick
Carl 23 Carl
Dick 24 Adam
And you want to take the value in column C, find it in column A and return the value in Column B. This is vlookup
D1 =VLOOKUP(C1,$A$1:$B$4,2,0)
D2 =VLOOKUP(C2,$A$1:$B$4,2,0)
D3 =VLOOKUP(C3,$A$1:$B$4,2,0)
D4 ...

1

An INDEX/MATCH combination retrieves only one value, just as you are seeing. If I were you, I would use the SUMIF function. It works like this: SUMIF(Range to test, Criterion, Range to sum). The third argument is optional. If you leave it off, it will assume that the range you want to summarize is the same as the range to test.
If I understand your example ...

1

You can usually use SUMPRODUCT for this - I'm not quite clear on the criteria for you but generically you can use this setup
=SUMPRODUCT((A2:A10=Z1)*(B1:J1=Z2),B2:J10)
where A2:A10 are the row labels, B1:J1 column headers and B2:J10 the data. Note the data range must be the same width as the column headrs and height as the row labels
Z1 and Z2 contain the ...

1

Let's deconstruct this thing
=if(condition,True,False)
=IF(L$3<$H11,4*5,(IF("<0",0,(5-(L$3-$H11))*4)))
so, L3 < H11 goto 4*5 - OK
L3 >= H11 goto If("<0",0,(5-L3-H11))*4 - OK
=If("<0",0,(5-L3-H11))*4
Let's try this one:
If "<0" goto 0 else goto 5-L3-H11*4
What is this if checking? =if("<0") doesn't return a true or false ...

1

If you have data in A1:A160 try this formula to find the number of different entries which contain "/11"
=SUMPRODUCT(ISNUMBER(SEARCH("/11",A1:A160))/COUNTIF(A1:A160,A1:A160&""))

1

So, with your data, my sheet looks like this -
So I want to list the owners in columns C:D, referencing whatever is in column A.
In C13
=INDEX($B$2:$B$9, SMALL(IF($A13=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), ROW($1:$1)))
In D13
=INDEX($B$2:$B$9, SMALL(IF($A13=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), ROW($2:$2)))
These are array formulas. Type them ...

1

Here's an example where the columns you want to check are A, B, and C.
Option Explicit
Sub DeleteIfBlanks()
Dim r As Long
Dim rng As Range
With ActiveSheet
For r = 1 To .UsedRange.Rows.Count
If LenB(.Cells(r, 1)) = 0 Then
If LenB(.Cells(r, 2)) = 0 Then
If LenB(.Cells(r, 3)) = 0 Then
If ...

1

Better to use DATE function in B4, DATE takes the following arguments
=DATE(Year,Month,Day)
so if you have the month number in B2, and day number in A4 then you can use this formula in B4
=DATE(2015,$B$2,A4)

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