## New answers tagged worksheet-function

2

I answer myself as I do this at least once a year and I feel the need of having a place to look for quickly when I forget something, plus I think this could be useful for someone else too.
So, ok, I know of two ways to do that:
using conventional formula
in D1 put =COUNTIF(A1:B10,"*"&C1&"*")
Some notes (mostly) for newbies:
those "*" in ...

1

Two formulas:
SUMPRODUCT:
=SUMPRODUCT((B1:B4<A1:A4)*(B1:B4<>"")*(A1:A4-B1:B4))
Or this array formula:
=SUM(IF((B1:B4<A1:A4)*(B1:B4<>""),(A1:A4-B1:B4)))
Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.
If the ...

0

The answer is =SUMIF
=SUMIF(A:A, A1, B:B)
However, if may be easier to update it and have the look up value in a new column, in this example column F, with the sum in column G. Screen shot shows the code.
G1: =SUMIF(A:A, F1, B:B)
G2: =SUMIF(A:A, F2, B:B)
etc

3

You can use array formula like the following:
=SUMPRODUCT(1*LEFT(A1:A3,FIND(" ",A1,1)-1))
where A1:A3 is the column of your data, and you have always a space between numbers and date, the formula finds the first space and read the number on the left and use Ctrl + Shift + Enter instead of 'Enter' for array formula, sumproduct will add the numbers.
In case ...

2

EDIT - MY MISTAKE - YOU CAN DO IT WITH a basic Excel functions - see
answer!
Here's another way using a VBA module, and custom public function
Insert this in VBA Code
Public Function SumLeftData(rgeData As Range) As Double
Dim celVal As Object
For Each celVal In rgeData.Cells
SumLeftData = SumLeftData + Left(celVal, 3)
Next
End ...

0

Format Cells - Date - Calendar Type (select Gregorian English) - Type (section has your required format Wednesday, March 14, 2001)

1

You can use SUMPRODUCT to calculate it:
=SUMPRODUCT(($A2:$A5=$E2)*(($B2:$B5=F$1)+($C2:$C5=F$1)))

0

Use one formula -- no need for multiple cells.
=IF(B2<=25,B2+24,B2-25)
This first checks whether subtracting 25 will result in a negative number or zero. If it will, do the same as you would subtracting 25 and then adding 49 -- this is a net result of adding 24 to the number. Otherwise, i.e., if the number is greater than 25, subtract 25.

1

Here you go:
=IF(A1<=10,A1*0.5,IF(A1>10,MIN(5,A1*0.25),""))
Basically, if A1 <= 10, then calculate 50% of A. Else, if A1 > 10, take the minimum of 5 and 25% of A1.

2

You can solve this using two small macros (to determine the name of the last sheet) in combination with the range operator and the INDIRECT() function.
First, create the following macro (Tools -> Macros -> LibreOffice Basic, create a new module or use the default module; paste the following code):
Function LastSheetName()
Dim nSheetCount As Integer
...

0

Yes, both are possible.
Put "X":
Use the formula: =IF(COUNTIFS($C:$C,F$1,$B:$B,$E2)>0,"X","")
Highlight cells:
Select your table then go to home - conditional formatting - new rule - use a formula to determine which cells to format.
Here you need just part of the previous formula: =COUNTIFS($C:$C,F$1,$B:$B,$E2)>0

3

Use IFERROR to Deal with the ones without "/":
=TRIM(IFERROR(LEFT(A1,FIND("/",A1)-1),A1))

2

Yes, you can use something like this in Column C:
=IF(LEFT(A1,1)="4",B1,"")
And, in column D:
=IF(LEFT(A1,1)="5",B1,"")
"LEFT" takes the left-most character of a string. In this example, the string is in "A1" (but change to whatever reference you need), and I'm specifying "1", to retrieve only one character from the left of A1.
If the left-most ...

2

Try this
=SUM(COUNTIF(A1,"Yes"),COUNTIF(C1,"Yes"))
The Countif() function specifically requires a range of cells. A list of cells is not a range.
Excel Help states,
COUNTIF(range, criteria) range Required. One or more cells to count, including numbers or names, arrays, or references that
contain numbers. Blank and text values are ignored.
...

0

Generally, you can program most anything with sophisticated macros, so yes, it is possible.
But it is not a natural application of Excel - it is designed to be a statically laid-out sheet with data, references and formulas; the concept of continuously changing content in a cell does not go well with that. There is little support for an iterative approach as ...

0

You could also use Data Validation, with understandable prompts and messages. The data validation formula, (assuming you want upper case for the letters) would be:
=AND(CODE(A1)>=65,CODE(A1)<=90,CODE(MID(A1,2,1))>=48,CODE(MID(A1,2,1))<=57,CODE(MID(A1,3,1))>=48,CODE(MID(A1,3,1))<=57)

1

Use concatenate:
=HYPERLINK(CONCATENATE("c:\Users\tminnick\Desktop\",A159,"_EmploymentAgreement.pdf"))
Concatenate will just stick all the strings provided as parameters into one long string, which Hyperlink will then happily accept.

0

Seems you need a little more VBA, so I have created the code below that incorporates the method described at the link. I do not know how you intend to use this, so the FormatCurrentCellPrice sub is just there as a proxy for your code that will point to the cell you want to format.
The FormatPrice sub determines the location and length of the text you want ...

0

You can't format specific characters of your cell's text if the text is returned by a formula.
You can, however, use VBA's Evaluate function to set the text and then access the Range.Characters collection of the target cell to set specific characters in bold.

0

Without VBA you can use Absolute References for M9 and M42 like the following:
="InterContinental Vienna @ "&$M$9&" "&$M$42&" p/room p/night"
Format the cell All Bold

0

If you just wanted to check the value and the checking logic was not too complex this could be done with standard Excel data validation. However, since you would like to reformat the value to be correct, you will need to use VBA or Visual Studio Tools for Office. The following is a simple VBA macro that does what you want.
The magic happens when the ...

0

Place the following Event Macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim B As Range, s As String
Set B = Range("B:B")
If Intersect(Target, B) Is Nothing Then Exit Sub
s = Target.Value
If Len(s) <> 3 Then GoTo fixit
If Not Left(s, 1) Like "[A-Z]" Then GoTo fixit
If Not IsNumeric(Mid(...

1

This is not possible within the type-in column.
You could add an extra (helper) column that contains the formula that splits out the pieces of your string, formats each one accordingly, and puts them nicely together.
if all your strings start with A, you could just tell everyone to drop the A (and the dash), and simply enter a number (but maybe you just ...

0

Use a formula to check if two cells match or not.
=IF(A4=E4,"Match","NoMatch")

0

With your pivot table already created, you can use the Calculated Field feature to create a sortable column within the Pivot Table.
In Excel 2013, the navigation is PivotTable Tools > Analyze > Options > Fields, Items, & Sets > Calculated Field ...
The formula I used is the same as @gtwebb: =noshow / (show +noshow )
After you have your ...

1

Use =SUM(IF(B2:B100="",A2:A100,B2:B100)) as a matrix formula (that means, hit CTRL+ALT+RET when entering it, not just RET). replace 100 with your last line of course.
That will make Excel loop for all lines, compare in each line i the cell Bi to "", and if it is blank, use Ai, otherwise use the Bi; and then make a sum of the whole thing.

1

To compare the 2 columns you can use this formula:
=IF(ISERROR(MATCH(A2,$B$2:$B$21,0)),"No Match","Match")
$B$2:$B$21 is the second column and 0 to have exact match, A2 is the first name in column A and you can drag it down.
It will give you Match if the Name is in Column A and Column B and No Match if not butif you want in the same column try sort ...

1

If you want to do this outside the pivot table the easiest way is to put in the formula manually.
If you click on the cell when inputting the formula you get =getpivot..., but you can manually type in =B2 and it will still reference that cell even if it is within the pivot table.

1

Click on the small arrow near clipboard to open it, copy each cell alone you will see it in the clipboard one over the other, go to the sheet where you want to paste it, double click on the cell to edit it, put the cursor where you want to paste the first 401.9 and click on it in the clipboard it will be pasted, move the cursor where you want Hypertension ...

1

You want to use INDIRECT():
=SUMIFS(INDIRECT(H4),array1,B4,array2,C4)

0

An option with a helper column.
The formula in Q2 is
=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))
and in R2
=IF(Q3="",Q2,"")
Copy down.

0

If you want the sum to be in the first row of each group you can use this formula:
=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")
Breaking it down:
The two INDEX(K:K,MATCH(1E+99,K:K)+1) find the last cell in column K that has a number and sets that as the ...

0

I used a different approach to find special characters. I created new columns for each of the allowed characters, and then used a formula like this to count how many times that allowed character was in each row entry (Z2):
AA2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AA$1,""))
AB2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AB$1,""))
...
Then I summed the number of allowed ...

0

Pivot table with the column you are trying to find duplicates in.
Count the number of times your value shows up.
Copy anything with count > 1 onto another sheet
Use this sheet as a VLOOKUP table, to flag duplicates on your original sheet.
Copy/Paste values from your VLOOKUP (because the formula is volatile, and you don't want Excel recalculating every time a ...

1

I got it working by the following method:
First I searched for \n and replaced it with <li></li>. This gave me starting and ending li on every new line in a single string output.
Then I searched for $ and replaced it with </li>. This gave me ending li at the end of the string that was outputted from above method.
Then I used the ...

4

The reason is that Excel internally works with floating point numbers.
That means that the 0.4 is internally represented as a bit sequence to the right from the decimal point, which represent 1/2, 1/4, 1/8, 1/16, etc., up to about 1/(2^31).
Therefore, anything that is not an integer (and is not exactly representable with the fractions listed above) has a ...

1

In Excel you need four rules, one for each color.
The yellow rule is
=MATCH(A1,Teams!$A:$A,0)
The other ones use the columns B, C and D on the Teams sheet. In earlier versions of Excel, pre 2010, I think, you cannot reference ranges on other sheets in a CF formula. You can use named ranges instead, though.

2

You don't need to array enter if the sum contains simple ranges or numbers. You can avoid array entering with Sumproduct().
To see how Excel processes the different formulas use the Evaluate Formula tool on the Formulas ribbon.
See a little animation about it here

1

A general answer:
Rather than trying to fuss with the results of the CELL("format"...) function, it may be easier to use a user-defined function to return the NumberFormat property of the cell directly.
In that case, the expression "=TEXT(A1,NumberFormat(A1))" would give you the displayed value rather directly.
To enable this, you need the following in ...

1

I don't quite understand why you need AX and AY, because if T and U contain real date and real time values (as opposed to text), then you can just use these in F.
But anyway, with one more helper column you could get to this: I inserted a new column B, which shifted the other columns to the right.
The formulas in row 2 are
B2 =IF(A2="15R",G2,B1)
C2 =G2-...

2

This appears to be working:
=IF(SUMPRODUCT( -- ISNUMBER(SEARCH({"en_US@","~?~?_~?~?@"},A1)))>0,MID(A1, FIND("@",A1)+1, FIND(";", A1, FIND("@", A1)+1)-FIND("@",A1)-1),"NA")
The magic is in the SUMPRODUCT( -- ISNUMBER(SEARCH({...},cell)))>0 bit, discussed here
To specifically SEARCH() for a "?", you have to delimit each one with a tilde since search ...

1

You can do it like this:
=IF(OR(ABS(D3-INT(D3)-(C3-INT(C3)))<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))-1)<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))+1)<TIME(0,15,0)),"Yes","No")
OR is there to test whether the actual time refers to day before or after. It's needed to work for these margin cases.
Note that C3+1 means whatever time in C3 + 1 day. ...

1

In my example, column A to D contain the same data as in your example. Column E, F and G are then filled with the formulas underneath.
You could add two columns with a date/time stamp. One for the schedule time which is in your case (column E):
=A2+C2
And one which is the start time (column F).
=IF(D2<C2-TIME(0,15,0),A2+1+D2,A2+D2)
This formula ...

0

you have to consider the date column.
A: Transaction Date
C: Schedule
D: Start time
E: Compliant
-15 Min column (F): =A6+C6-TIME(0,15,0)
+15 Min column (G): =A6+C6+TIME(0,15,0)
Compliant column (E): =--AND(A6+D6>=F6,A6+D6<=G6)
Result for compliant: (0 = No, 1 = Yes)
you could also do it without the -/+ 15 Min column:
=--AND(A6+D6>=A6+...

0

I'm thinking it can't be done with how you have it set up currently. Just using the TIME functions, Excel can't differentiate between yesterday and today. In the background TIME goes from 0 at midnight to .99999999 at 11:59:59 pm. It doesn't rollover to 1.0000, 1.0001, etc. to the best of my knowledge.
If you can pull the full time stamp into your ...

1

In A1 enter:
=TEXT(4*(ROW()-1)+1,"000") & "-" & TEXT(4*(ROW()-1)+4,"000")
and copy down:

1

You'll need a basic IF statement here.
If you write it out you can see how it will work:
If the value in A1 is greater than 11000, calculate 20% of the difference. If not, display 0.
Excel IF statements work like this: =IF(logical_test, value if true, value if false)
If we take our written statement and translate it to the formula we get this:
=IF(A1 &...

0

You have to write the date so Excel and Vlookup can understand it as Date, Try the function Date(year,month,date) for example:
=Vlookup(Date(right(B7,2),C6,A1),Range,Column,False)

0

There are probably other ways to accomplish this, but here's an option. You might be able to use a COUNTIFS function. However, that will only let you search by 3 criteria.
If you're saying every record has Site, Type, and Actual Rec, then you might be able to search with those three criteria.
Here's a screenshot of something I threw together:
On your ...

0

The solution i use where you have only a few cells that i want both calculations and the abillity to enter values to override the formula or delete what they entered and have the formula work is.
Setup your worksheet as you want..
Then for the cell you want to override setup an identical cell in a column off screen, ie column az and cut and paste your ...

Top 50 recent answers are included