Tag Info

Hot answers tagged

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")


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

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

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