## Hot answers tagged worksheet-function

5

Yes Excel always calculates hidden rows unless your formula or VBA code is ignoring hidden rows on purpose. You can take a look at this Technet blog for more information. Here is a relevant quote tho.
Normally, hiding rows won't interfere with any formulas so your
results will remain the same even if values are called from hidden
cells.

5

I made a solution using VBA:
Public Function BikeSize(MinSize As Integer, MaxSize As Integer, datainput As String)
Dim dataoutput() As Variant
ReDim dataoutput(0)
BikeSize = 0
datasplitted = Split(datainput, " ")
arraysize = UBound(datasplitted)
j = 1
For i = 0 To arraysize
m = datasplitted(i)
If m >= MinSize ...

4

Answering my own question as I think this may be useful for someone else too.
CALC :
In LibreOffice Calc go to Menu "Tools" > "Options", then "Libre Office Calc" > "Formula" and check on "Use English function names".
Then also set "Formula syntax" to "Excel A1", this will make formula syntax more compliant to Excel "default form", for example, for the ...

4

Use the IF to create a condition, AND because you need to match multiple conditions, and ISBLANK for your conditions:
=IF(AND(ISBLANK(B1), ISBLANK(B2)), "", A3+B2-B1)

3

This array formula works for me -
=IF(AND(A1:A3>TODAY(),B1:B3="Yes"), "Yes", "No")
Type it and hit ctrl+shft+enter - you will see curly brackets appear.

3

I believe that @fixer1234 is correct — it's floating point rounding error
on the time values. I don't fully understand what's happening,
but I was able to reproduce it on my system, and I found a work-around:
Change your formula to
=INDEX(B:B, MATCH($I$4+TIME(0,0,1), A:A))
This adds one second (TIME(0,0,1);
the arguments are TIME(hours,minutes,seconds))
...

3

Try some variation of this:
A B C
1 =rand() =large($b$1:$b$10,a1)
2 =rand() =large($b$1:$b$10,a2)
3 =rand() =large($b$1:$b$10,a3)
4 =rand() =large($b$1:$b$10,a4)
5 =rand() =large($b$1:$b$10,a5)
6 =rand() =large($b$1:$b$10,a6)
7 =rand() =large($b$1:$b$10,a7)
8 =rand() =large($b$1:$b$10,a8)
9 ...

3

This is not hard to do with functions/formulas
if you’re dealing with only two columns.
You’ll need three “helper columns” in addition to a results column,
and you’ll need to identify a string that never appears in your data.
Let’s use columns E through H, and the string /:
E1 — =IF(A1<B1, A1, B1)
F1 — =IF(A1<B1, B1, A1)
G1 — =E1 & "/" & F1
...

3

You can use the following:
=IF(ISBLANK(AG2),"",D2&" "&AG2)
The IF checks if AG2 is blank. If TRUE (AG2 is blank), it returns blank (“”). If FALSE (AG2 is not blank), it returns the combination of D2 and AG2 separated by a space. You can add as many strings you like using the & symbol. You can point to a cell or type your string surrounded by ...

2

Generic VBA solution with regular expressions
This method should cover your needs. It can also be used by other users to extract multiple strings from a given string with the help of regular expressions
Open your VBA editor (ALT+F11)
Insert a new module (!) and paste the below code into it
Go back to Excel and use this formula in a cell where you want ...

2

Sub ConvertToText()
Dim i As Long, lastRow As Long
lastRow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRow
Sheets("Sheet1").Cells(i, 1).Formula = "'" & Sheets("Sheet1").Cells(i, 1).Formula
Next i
End Sub
Assuming your data is in column A, use this VBA code. Open the VBA editor by pressing Alt+F11, insert a Module ...

2

F2, Same as in Microsoft Excel.
Once done you can press Enter to switch to next row.

2

You can calculate a new column in both tables where you remove all dots and spaces and replace "&" to "AND", that will work for your examples:
`=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(original_value," ",""),".",""),"&","AND")

2

Basically what you are looking for is called a LOOKUP function in excel.
Step-1: Make a list of X and Y (in your case DAYS and FRUITS) - let us say this range is A1 to B7
Step-2: Write the following formula in the cell where you want the fruit name to appear =VLOOKUP(A8,A1:B7,2,0) (assuming that A8 contains your DAY)

2

Assuming Microsoft Excel, the "," in your formula indicates that the function will return the string , if 1000 is true (which upon testing happens to be true).
Microsoft Excel - IF function
Use the IF function, one of the logical functions, to return one value
if a condition is true and another value if it's false.
Syntax: IF(logical_test, ...

2

Well, I made a quick attempt and this and came up with a rough version that works. It probably isn't the best so hopefully someone responds with a more elegant solution, but try this:
=IF(AND($B$2<=$B$1,$B$3<=$B$1),"Good",IF(AND($B$3>$B$1,$B$2>$B$1),"Bad", "Neutral"))
B1 = 0.83 (just my value for budget)
B2 = YTD value entered by user
B3 = MTD value ...

2

Here’s a tricky way (that might be considered elegant).
I’ll assume that < 83 is the “good” condition. Use this formula:
=CHOOSE((D1<83)+(F1<83)+1, "bad", "neutral", "good")
Logical expressions like D1<83 evaluate to one of the Boolean values
TRUE and FALSE, which are numerically equal to 1 and 0, respectively.
By adding ...

2

Your problem is that the cells aren't blank. The cells are "" which we see as blank because it is an empty text string but is not actually blank. That leads to silliness like 0 + "" in your formula for J.
To fix the I formula, try this:
IF(ISBLANK(H22),"",IF(ISBLANK(F22),"",H22*G22))
I'm not sure what you're trying to accomplish in J though. It looks like ...

2

To start out, first lets work with one cell. B1.
=IF(B1="","",SUM(A3+B2-B1))
This will return blank if B1 is empty or will do the sum. Now we can just wrap that in another if.
=IF(B2="","",IF(B1="","",SUM(A3+B2-B1)))
The problem with ISBLANK is that if there is a formula in that field, it will not return blank, even if there is no number in the field.

1

Use this guide. And just make the text you're looking for an '=' sign. The '=' will denote a formula.
Then apply the same rule to the rest of column.
You can then do the same and make the text not looking for an '=' sign.

1

The formula is:
=IF(DAY(A2)<10,EOMONTH(A2,3)+15,EOMONTH(A2,4)+1):
if the original date is at the end of the month DAY(A2)<10, then it's shifted by 4 month (3 months plus end of the month) +15 is the 15th of the desired month.
if the original date is middle-month, then +4.5 months means 1st of 5th month (end of 4th month + 1 days).
The formula ...

1

Autofill will only add ONE per 'moved' row/column to cell-references, but this can be used to achieve the intended result...
Try this:
=OFFSET($C$2,(ROWS($C$2:$C2)-1)*10,0)
In this all references to cells start at $C$2 (first parameter).
It will stay as is, due to the $-s.
The result from ROWS(...) will increase by one for each extra line it gets copied ...

1

### Look up specific string in row (not column) and return value of cell x columns left of that location

Actually I realised this is very easy to do with the LOOKUP() function. In Sheet 2 Cell B1 I would put: =LOOKUP(A1,'Sheet 1'!D1:F1,'Sheet 1'!A1:C1)

1

I know of no way to do it directly without VBA. But if you select all sheets that need to be changed then changing it on one sheet will propagate the change to all sheets. (I don't select multiple sheets very often, but it's either Ctrl+click or Shift+click on the tabs. I think Ctrl+click selects individual sheets and Shift+click selects all sheets from the ...

1

You're overthinking this:
(131-130) + (135-131) + (140-135) + ...
= 131 - 130 + 135 - 131 + 140 - 135 + ...
= -130 + 131 - 131 + 135 - 135 + 140 ...
= -130 + (131-131) + (135-135) + ...
= -130 + 0 + 0 + ... + 120
= 120-130
i.e., the sum of the differences between pairs of consecutive numbers
is simply the difference between the last number ...

1

This method is overkill in this case, but you could also do this with an array formula:
=AVERAGE((A2:A7)-(A1:A6))
To enter an array formula, type it in, then, with your cursor still in the formula editing field, press the combinationCtrl+Shift+Enter. The formula should now should show up as
={AVERAGE((A2:A7)-(A1:A6))}
...but you can't type those ...

1

You can use an array formula entered with ctrl+shift+enter. Search is not case sensitive so that should be fine.
=OR(IFERROR(SEARCH(SiteCategories[Category1],A2),0))
Here it is setup to handle blank lines in the table thanks to Kyle in the comments.
=OR(IFERROR(IF(SiteCategories[Category2]<>"",SEARCH(SiteCategories[Category2],A2),0),0))

1

Using a nested IF statement, you can check if C2 is Noun or Verb. Then tell is what to do in each case.
=IF(C2="Noun", A2, IF(C2="Verb", "[" & A2 & "]", " "))
The first if checks C2 for the word Noun. If it is present, it returns the A2 to the cell. If is is not Noun, it looks to see if the word is Verb. If it is, it returns the word from A2 with ...

1

If you actually wanted the quotes then
=IF(C12="Noun",""""&A12&"""",IF(C12="Verb","["&A12&"]",""))
would be the formula you want in B.

1

I'm not clear what you want. You say,
I don't want zeros to print if a cell in F or H is empty.
Based on that, it seems to me that you want to have
=IF(OR(ISBLANK(F22),ISBLANK(H22)), "", G22+I22)
in J22. See also Display Blank when Referencing Blank Cell in Excel 2010
for other approaches.

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