## New answers tagged worksheet-function

1

I would recommend using countifs, which allows to use multiple ranges and multiple conditions without making the formula statement too complicated.
The general format is the below:
COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
Which for your case would like this:
...

0

It appears that Excel treats "true/TRUE" and "false/FALSE" as magic strings when they appear in formulas -- they are treated as functions: TRUE() and FALSE() instead of strings.
If you need to count true and false in a column, you will not get a count if you use "true" or "=true" as the criteria. There are some approaches you can use to count true and false ...

0

You want to disappear start and ending " and others to remain?
Option 1
Use text editor with RegExp to text file (for example: Notepad++). Find and replace (sect option to allow RegExp)
Find: ^\"(.+)"\t"(.+)\"$
Replace: $1\t$2
Now you can import without text qualifier.
Option 2
Import without text qualifier selected and use MS Office RegExp (in ...

1

F3:F11 is a range, but you're entering it in a place where it's only appropriate to give a single value, so Excel tries to choose one value from the range, using the following rules:
If the range is in a single column (as this is) Excel chooses the cell from that column in the same row as the referring cell (or the error #VALUE! if the range doesn't ...

0

Let's say you have the following data in 'Sheet 1':
Col A ; Col B ; Col C
1 ; 131 ; 3
2 ; 125 ; 5
3 ; 168 ; 2
4 ; 154 ; 8
5 ; 1365 ; 0
If I understand the requirement of your question correctly,
use the following formula in the sheet you want to have your corresponding value ...

0

Highlight both columns > conditional formatting (home tab) > highlight cell rules > duplicate values.
This will highlight all duplicates in both columns.
Make sure you are highlighting the columns and not the cells.

2

Put these two subs in a standard module and the run MakeButton
Sub MakeButton()
ActiveSheet.Shapes.AddShape(msoShapeOval, 228#, 557.25, 126.1, 126.1).Select
With Selection
.Characters.Text = "1 ADDER"
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 3
.ShapeRange.Line.Visible = msoTrue
...

0

Given your comments as well as your question, it seems you want to return TRUE if any word in one phrase matches a word in the adjacent phrase. One way to do this is with a User Defined Function (VBA). The following excludes any words that are in arrExclude, which you can add to as you see fit. It will also exclude any characters that are not letters, ...

1

I have two solutions for you:
Convert your data to a table (insert - table) and you won't need to worry about formula auto fill when you add new data, so your users range will be really the range you use.
If the previous solution doesn't work: select a cell in a data column (not one with pre-filled formulas that extend forever) and press CTRL+ DOWN ...

0

As described in the other answer you can do it with formula but it may not be the best / easiest solution, I'd use one of the below options:
Pivot table:
Probably the easiest and most elegant solution if you can live with its limitations:
- destination range is not editable (maybe its even a positive in your case).
- you can use only number and date ...

0

It is possible to find the values with formulae. You'll just need the headers to show numbers, e.g. 1 with the format of 0 "st visit" etc.
Unfortunately the functions will never deal with two things: data loss and overflow. Imagine the patient coming on an 8th occasion and it not showing in your table 'cause you've only filled it to 7.
The function ...

0

I think most elegant way to do this with a pivot table: Set your name column as column label, set your number column as values and select to summarise it by minimum.

0

=OR(COUNTIF($A$2:$A$18,A2)=1,SUMPRODUCT(1*(B2>$B$2:$B$18),1*(A2=$A$2:$A$18))=1)
You need to change the direction. It needs to be one position higher than the lowest not one position lower than the highest.
Rereading the post led me to believe OP looks for the lowest value. In this case you're looking for the value 0 position from the lowest (...))=0)), ...

0

In cell C1 use this formula Link
=IF(VLOOKUP(B1:B4,A:A,1)=B1,"",B1)

1

The most difficult part of this exercise is determining what, in column A, constitutes a last name. In your example, it's either:
The first word, if there's a comma in the whole name
The second word
If that rule is true, then you can just do a formula like this:
=NOT(ISERROR(FIND(last_name, B1:B4)))
The formula to actually determine the last name is a ...

0

Let's assume that your data range goes from A2:B100, with headers in A1:B1. Assuming you only want to identify what column A cells contain a single value, 22, you would build two columns. One to identify the successes in column A, and one to identify the successes of column A and column B. You could also do this in a single column if you like.
Column C will ...

1

I'd do it with pivot chart: put KRA to "axis fields" and total rating to values (use max function).
To facilitate updating of your chart convert your data range to data table first so you won't need to change source data range when you enter data.
Also you can add very simple code to your sheets module to update chart when data changes. (just change ...

0

I'm not sure what the importance of the quotations is (unless you have lots of data in each cell and you are only after the data with quotations as your search criteria). Then you may need a vba function to split your data.
If column A is filled with your mixed data and column B is blank and you only want to check cells against their counterparts in column ...

0

You could use an array formula that will work with unsorted and repeat dates.
The formula in F2 is
=LOOKUP(2,1/(($B$2:$B$6=E2)*$A$2:$A$6=MAX(IF($B$2:$B$6=E2,$A$2:$A$6,0)))),$C$2:$C$6)
This is an array formula and must be confirmed with Ctrl-Shift-Enter. Then you can copy it down.
Note that in the screenshot the latest date for event A is in the first ...

0

The question is a little vague. You state that you want the "latest date" in the count column?
If you only want the latest date, Pivot Tables are the way to go or alternatively, possibly the Microsoft PowerQuery addin.
I assume you really want the count to show the total number of records for the campaign but to only show the latest campaign date?
Again, ...

1

Give this a try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 3).Value = Target.Value
Target.Clear
Application.EnableEvents = True
End Sub

0

With regard to limits on the SUM() function, I filled ALL of column A with the value 20 and =sum(A:A) calculated properly.
If there are any errors in column A or text values, then the array formula:
=SUM(IF(ISNUMBER(A:A),A:A))
should work.
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

1

First install the following event macro in the worksheet code area:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim A As Range
Set A = Range("A:A")
If Intersect(Target, A) Is Nothing Then Exit Sub
Cancel = True
Cells(Target.Row, 3).Interior.ColorIndex = 27
End Sub
If you double-click on any cell in ...

0

This VBA code should work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Set wks = ActiveSheet
theRow = Target.Row
theColumn = Target.Column
theValue = Target.Value
If theRow = 1 Then
If theColumn = 1 Then
Application.EnableEvents = False
wks.Cells(1, 4) = theValue
Target.Clear
...

0

You may write your own function using StarBasic/LibreOffice Basic or Python. This is very easy and allows for updating a function which affects all cells where the formula is used.
As described here (courtesy Louic's weblog), just do the following:
Go to Tools -> Macros -> Organise Macros -> Libreoffice Basic;
Select Module1;
Click edit;
Enter the ...

0

As an addendum to Phrogz's answer, if you need to check whether a cell in a column is hidden, try either of the following,
Conditional Formatting
=CELL("width",TargetCell)=0
This updates automatically as soon as a column is hidden.
Formula Check
=IF(CELL("width",TargetCell)=0, "Cell is hidden.", "Cell is visible.")
This formula will not update ...

0

=IF(OFFSET('Sheet 1'!$A$1,MATCH('Sheet 2'!A1,'Sheet 1'!$A:$A,0)-1,1)<>"",OFFSET('Sheet 1'!$A$1,MATCH('Sheet 2'!A1,'Sheet 1'!$A:$A,0)-1,1),OFFSET('Sheet 1'!$A$1,MATCH('Sheet 2'!A1,'Sheet 1'!$A:$A,0)-1,1))
What you requested for the unfound values is the same you requested for found values, if you really want to fiddle around with shifting based on ...

1

You can use SUMPRODUCT as long as you have the right syntax, like this
=SUMPRODUCT((AH6:AS36="A")*AG6:AG36)
in fact SUM also works but you would need to "array enter" if you use SUM, i.e.
=SUM((AH6:AS36="A")*AG6:AG36)
confirmed with CTRL+SHIFT+ENTER

0

Straight forward enough.
Firstly I would move all your readings to Column B as it will look better on your graph.
Then in A1 put in: 00:00
Then in A2 put in: 00:05
Next highlight A1 and A2, and a black square dot will appear in the bottom right of A2, drag that down as far as you want it to go, The time will increment by 5min.

0

The easiest way to summarize this information is with a Pivot Table. A Pivot Table is used to group and summarize data. In this example I grouped by the Code and then set the value to the USD field, and made sure to tally by sum.

0

As Raystafarian says, this can be done quite simply by using SUMIF. The syntax for SUMIF is quite simple, in your case a sample formula could look like this:
=SUMIF(B2:B6,100,A2:A6)
As you can see, it takes three arguments:
The first is the range which you want to evaluate against some criteria
The second is the criteria you want to compare the values ...

0

IFERROR is a very convenient function;
IFERROR(calculated_value, default_value)
is short for
IF(ISERROR(calculated_value), default_value, calculated_value)
i.e., it substitutes a default value (typically a non-error value)
for an error value.Â So you probably want something like
IFERROR(AVERAGE(E8:E19), "")
replacing the "" with whatever you want to ...

0

Have you tried using the error-catch function in Excel? As you have said, if you take the average of only blank cells, Excel will provide a result of Div/0.
Let's say there are 3 people (A,B,C) reviewing a recipe for Guacamole. Let's say the csv looks something like this:
Ingredient,Score A, Score B, Score C
Avocado,1,2,3
Cilanto,,,
Lime,1,2,3
...

2

Calculate once, copy the range of formulas, then Paste Special (right-click target, then S) the Values only back into the range. This replaces the formulas with their results and will prevent the random passwords from being recalculated.

0

The very concept of formulae is that they fire again and again. You can disable them from doing so by fixing their values only. If you want a fix value you may resort to using VBA.
I would add a Sub that creates the password when you call it, like this:
Sub ertdfgcvb(Target As Range)
For Each Cell In Target
Cell.Formula = ...

0

Add a validation rule. If the entered value is not in Time/Date format (which usually means it is String) then your formula will default. I agree with @jcbermu, you most probably have issues with regional rules and this is the way to detect whether or not you use valid dates.

0

No, there's no way to declare a variable for a cell reference inside a formula. You just have to refer to the cell each time you need to reference its value.
As for simplifying your age calculation formula, you might try the following:
=ROUNDDOWN(YEARFRAC(B5,TODAY()),0)

0

You actually don't need the helper sheet to achieve this, try this formula: ="HRM: "&text(today(),"dddd")&" the "&text(today(),"d")&MID("thstndrdth",MIN(9,2*MOD(day(TODAY()),10)*(MOD(day(TODAY())-11,100)>2)+1),2)&" of "&text(today(),"mmmm")&" "&year(today())

0

One answer is to develop your solutions using Names for both Ranges and Formulas.
Thus, the look of your formula may be improved by defining 'v' to refer to =C2:C14. The formula =MAX(LEN(v)) still needs to be array entered but, if you define 'n' to refer to =LEN(v), the array calculation is carried out as the default behaviour of a named formula. Therefore ...

1

What kinds of controls are those? The easiest solution is to add Form Controls (as opposed to ActiveX Controls).
After that, right-click on them and click on "Format Control..." at the bottom of the context menu. In the window that opens, go to the "Control" tab - if you're not there already - and select a range for the "Cell link:" field.
Now, any ...

2

You can do this with an array formula. For a fixed range, the formula in C2 would be
=IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no")
Be sure to enter this as an array formula using Ctrl+Shift+Enter. You'll know it worked when you get curly brackets { } on either end of the formula.
If you want the range to be dynamic, one good way is to define ...

0

Couldn't get the above to work, as the formula would return the largest column (number) that was non-zero. I used =MATCH(SMALL(J10:N10,1),J10:N10,0). The small ignores zero values.

0

Try this small User Defined Function (UDF):
Public Function MultiMatch(sIN As String, rng As Range) As String
Dim r As Range
MultiMatch = "no"
For Each r In rng
If InStr(1, sIN, r.Text) > 0 Then
MultiMatch = "yes"
Exit Function
End If
Next r
End Function
User Defined Functions (UDFs) are very ...

0

This can be achieved with a custom number format that consists of 9 zeros 000000000
Source Using a custom number format to display leading zeros
In Microsoft Excel, you can use either of two methods for using a custom number format to display leading zeros. Use one method for fixed-length numbers and use the other method for variable-length numbers.
...

0

The easiest way to do this will be to set up a helper column that that gives the bi-weekly point totals for each employee. Once you have that in place, you could replace the current point-totaling formula on sheet 1 with something like this:
IF(COUNTIF([range containing bi-weekly point totals],[criteria is
"0"]) = 0,[original point total ...

0

Not sure I totally understand the question, but I THINK you may want
=SUMIF(B4:Z4,"UK IR",B1:Z1)
which means: Sum all cells in row 1 where row 4 equals "UK IR"
Edit:
In the sample file the sum formula is in cell C20 with a simple =SUM(D20:AS20)
You only want to include values in the sum where row 4 contains the text "UK IR"
So, the formula you need is a ...

0

Define a named for the hyperlink target cell, then set the hyperlink to that named range. When rows are inserted or deleted, the named range will still point to the correct cell.

0

Since SUMIF() can be applied only to single row or column, your evaluation must be carried out in 2 steps.
First use SUMIF() on rows and also concatenate the condition values for each summation in an adjacent cell. This concatenated string will serve as a key in next step.
Next apply SUMIF() to the column that contains row sums, using the "key" from ...

0

I had this issue. In my case the tilde character (~) broke the MATCH function, even with pasted values. There may be other special characters that do this as well.

1

I'm using excel 2010 and just highlight the two columns that have the two sets of values I'm comparing, and then click the Conditional formatting dropdown on the home page of excel, choose the Highlight Cells rules, and then differences. It then prompts to higlight either differences or similarities and asks what colour highlight you want to use...

Top 50 recent answers are included