## Linux HowTo: Excel count unique values if second criteria is also satisfied

I currently have a formula to count all unique values of a range, however I would like to expand this count all unique values, if a second column meets a second requirement, for example.

``````      A        B
1  Name     Location
2  Tim      Onsite
3  Tim      Onsite
4  Ben      Offsite
5  Lisa     Onsite
``````

In this instance the unique values of Name will return 3, for that I am using

``````=SUMPRODUCT(1/COUNTIF(A2:A5,A2:A5&""))
``````

However when I try to use a `COUNTIFS` it always returns an error.

``````=SUMPRODUCT(1/COUNTIFS(A2:A5,A2:A5&"", B2:B5, "Onsite"))
``````

Any ideas how to achieve this, so from my sample data, counting `Onsite` would return 2, (3 unique values, 2 of them satisfying the onsite criteria)

You need a bit more conversion:

`=SUM(IFERROR(1/COUNTIFS(A2:A5,A2:A5&"", B2:B5, "Onsite"),0))`

This is an array formula, so after inserting you need to press CTRL + SHIFT + ENTER

Spill functions make this a bit easier

=UNIQUE(IFS(“onsite”=locationRange,nameRange))

I’d put an IFNA() around it just to be safe.

## Server Bug Fix: Incorrect values obtained through Excel formula

Employing the well-known physics formula `h=gt^2/2`, we are required to display the theoretical time it would take for an object to reach the ground. I isolated variable `t=sqrt(2h/g)` and inserted formula `=SQRT(QUOTIENT(2*B3,\$B\$1))`, yet the values reaped were incorrect.

Source data: • Expected valid results for `t = 1` to `t = 8` are respectively `.45`, `.64`, `.78`, `.90`, `1.01`, `1.11`, `1.19` and `1.27`.

``````=SQRT(2*B3/\$B\$1)
`````` The above results are calculated to Excel’s precision limit and then rounded to two decimals by the cell formatting option. There is no easy way to exactly mimic your stated desired results.

The `QUOTIENT` function returns only the Integer part of the division, which is why using it in your formula will return 0’s or 1’s, depending on the value of `t`

You may try these:

``````=SQRT(TRUNC(2*B3/\$B\$1))
``````
``````=SQRT(ROUND((2*B3/\$B\$1),0))
``````

Tagged : /

## Making Game: Excel count unique values if second criteria is also satisfied

I currently have a formula to count all unique values of a range, however I would like to expand this count all unique values, if a second column meets a second requirement, for example.

``````      A        B
1  Name     Location
2  Tim      Onsite
3  Tim      Onsite
4  Ben      Offsite
5  Lisa     Onsite
``````

In this instance the unique values of Name will return 3, for that I am using

``````=SUMPRODUCT(1/COUNTIF(A2:A5,A2:A5&""))
``````

However when I try to use a `COUNTIFS` it always returns an error.

``````=SUMPRODUCT(1/COUNTIFS(A2:A5,A2:A5&"", B2:B5, "Onsite"))
``````

Any ideas how to achieve this, so from my sample data, counting `Onsite` would return 2, (3 unique values, 2 of them satisfying the onsite criteria)

You need a bit more conversion:

`=SUM(IFERROR(1/COUNTIFS(A2:A5,A2:A5&"", B2:B5, "Onsite"),0))`

This is an array formula, so after inserting you need to press CTRL + SHIFT + ENTER

Spill functions make this a bit easier

=UNIQUE(IFS(“onsite”=locationRange,nameRange))

I’d put an IFNA() around it just to be safe.

## Code Bug Fix: Range Error when using application.worksheetfunction.Vlookup in VBA excel

i have the following piece of code:

``````Function MyLookup(var1 As Variant, range1 As Range, var2 As Integer) As Double
MyLookup = Application.WorksheetFunction.VLookup(var1, range1, var2, False)
End Function
``````

I use this function to calculate some of the parameters in the following function

``````Function Test(IntMethod As String, CFDate As Date, ValDate As Date, _
CF As Double, RepFreq As Integer, DiscToDate As String, _
DiscType As String) As Double
Dim InterpPeriod As Double, PrevDateRate As Double, _
CurrDateRate As Double, PrevCurvMat As Integer, _
CurrCurvMat As Integer, rate As Double
Dim Yield_Curves As Range, PrevValDate As Date

Set Yield_Curves = DISC_CFS.Range("Yield_Curves")
PrevValDate = Last_Period(ValDate, CFDate, RepFreq)
'-----------------------------------------------------
InterpPeriod = Application.WorksheetFunction.YearFrac(CFDate, ValDate, 1)
PrevCurvMat = YearsDiff(ValDate, CFDate)
CurrCurvMat = YearsDiff(ValDate, CFDate) + 1
PrevDateRate = MyLookup(PrevValDate, Yield_Curves, 2)
CurrDateRate = MyLookup(ValDate, Yield_Curves, 2)
``````

when i get to MyLookup function, the range is not recognised and it exits the function.
Any hints?

I have finally managed to resolve the issue. The error was in one of my lookup variables, which was a date. The date should be converted to long type.

the correct code is the following:

``````Function MyLookup(var1 As Variant, range1 As Range, var2 As Integer) As Double
MyLookup = Application.WorksheetFunction.VLookup(var1, range1, var2, False)
End Function
``````

And for the main code:

``````Function Test(IntMethod As String, CFDate As Date, ValDate As Date, _
CF As Double, RepFreq As Integer, DiscToDate As String, _
DiscType As String) As Double
Dim InterpPeriod As Double, PrevDateRate As Double, _
CurrDateRate As Double, PrevCurvMat As Integer, _
CurrCurvMat As Integer, rate As Double
Dim Yield_Curves As Range, PrevValDate As Date

Set Yield_Curves = DISC_CFS.Range("Yield_Curves")
PrevValDate = Last_Period(ValDate, CFDate, RepFreq)
'-----------------------------------------------------
InterpPeriod = Application.WorksheetFunction.YearFrac(CFDate, ValDate, 1)
PrevCurvMat = YearsDiff(ValDate, CFDate)
CurrCurvMat = YearsDiff(ValDate, CFDate) + 1
PrevDateRate = MyLookup(CLng(PrevValDate), Yield_Curves, 2)
CurrDateRate = MyLookup(CLng(ValDate), Yield_Curves, 2)
``````

thank you for the provided answers and assistance

Tagged : / / / /

## Linux HowTo: Insert line break for a specific character within a cell every time it is repeated

I have a cell that look like below. There is a line break between each line.

``````[User.x]: Bill
[User.xxxxx]: Johnathan
[User.xxx]: Erica
``````

The amount of users within each cell varies from cell to cell as well as their names.
I need to insert a second line break between each line to produce

``````[User.x]: Bill

[User.xxxxx]: Johnathan

[User.xxx]: Erica
``````

so that it is all within the same cell. This needs to be done for the sake of readability.
I have tried to come up with a formula that triggers every time it sees ‘[User.’ , but the problem I am having is it only triggers on the first case. Not the second and third case. Any tips or ideas? Thank you!

Just replace each single hard return with a pair of hard returns:

``````=SUBSTITUTE(A1,CHAR(10),CHAR(10) & CHAR(10))
`````` ## Making Game: Insert line break for a specific character within a cell every time it is repeated

I have a cell that look like below. There is a line break between each line.

``````[User.x]: Bill
[User.xxxxx]: Johnathan
[User.xxx]: Erica
``````

The amount of users within each cell varies from cell to cell as well as their names.
I need to insert a second line break between each line to produce

``````[User.x]: Bill

[User.xxxxx]: Johnathan

[User.xxx]: Erica
``````

so that it is all within the same cell. This needs to be done for the sake of readability.
I have tried to come up with a formula that triggers every time it sees ‘[User.’ , but the problem I am having is it only triggers on the first case. Not the second and third case. Any tips or ideas? Thank you!

Just replace each single hard return with a pair of hard returns:

``````=SUBSTITUTE(A1,CHAR(10),CHAR(10) & CHAR(10))
`````` ## Linux HowTo: Apparent false syntax error using UNIQUE() in COUNTIF()

I have a spreadsheet with the following values for `A1:A8`:

``````1   1   1   2   3   3   4   5
``````

I want to count the number of unique values `>= 3`. (The values aren’t guaranteed to be sequential or with uniform step sizes)

This would seem trivial using `UNIQUE()`, but oddly doesn’t work.

If I set `B1` to `=UNIQUE(A1:A8)`, the result is a new column with the values we’d expect

``````1   2   3   4   5
``````

However, if I then set `C1` to `=COUNTIF(UNIQUE(A1:A8), ">=3")`, I get the standard syntax error:

``````There's a problem with this formula.
Not trying to write a formula?
[...]
To get around this, type an apostrophe ( ' ) first
[...]
``````

Obviously, if I remove the call to `UNIQUE()` (`=COUNTIF(A1:A8, ">=3")`), everything works. I just get the wrong result, since it obviously counts the two `3`‘s.

Trying to see if the problem is with `UNIQUE()`, I tried replacing `COUNTIF()` with `AVERAGE()` (`=AVERAGE(UNIQUE(A1:A8))`), but that works.

Is this expected behavior?

The problem is UNIQUE returns an array and COUNTIFS does not work with arrays.

Filter first and use COUNT:

``````=COUNT(UNIQUE(FILTER(A1:A8,A1:A8>=3)))
`````` What is interesting is if you use helper cells and spill `UNIQUE` then refer to that it works. `B1` = `=UNIQUE(A1:A8)` and `C1` = `=COUNTIF(B1#,">=3")` This works because the formula creates a `Spill Range` that the `B1#` refers to. It is like a named range.

:Caveat:

This method works with any version of Excel: 1. An Array (CSE) Formula in cell `I55`:

``````{=SUM(--(FREQUENCY(IF(G55:G62>=3,G55:G62),G55:G62)>0))}
``````
2. Finish formula with Ctrl+Shift+Enter.

How it works:

• `FREQUENCY` returns an array of values that corresponds to filtered set of data for the data array.

• The `IF` function filters data.

`IF(G55:G62>=3,G55:G62`

• Then each of these values tested to be `greater than or equals to 3`, and looks like this.

`{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}`

• `SUM` adds these values & returns the total, is `3`.
• This formula can be extended to handle multiple criteria.

``````{=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0))}
``````

N.B.

Adjust cell references in the formula as needed.

Tagged : /

## Making Game: Apparent false syntax error using UNIQUE() in COUNTIF()

I have a spreadsheet with the following values for `A1:A8`:

``````1   1   1   2   3   3   4   5
``````

I want to count the number of unique values `>= 3`. (The values aren’t guaranteed to be sequential or with uniform step sizes)

This would seem trivial using `UNIQUE()`, but oddly doesn’t work.

If I set `B1` to `=UNIQUE(A1:A8)`, the result is a new column with the values we’d expect

``````1   2   3   4   5
``````

However, if I then set `C1` to `=COUNTIF(UNIQUE(A1:A8), ">=3")`, I get the standard syntax error:

``````There's a problem with this formula.
Not trying to write a formula?
[...]
To get around this, type an apostrophe ( ' ) first
[...]
``````

Obviously, if I remove the call to `UNIQUE()` (`=COUNTIF(A1:A8, ">=3")`), everything works. I just get the wrong result, since it obviously counts the two `3`‘s.

Trying to see if the problem is with `UNIQUE()`, I tried replacing `COUNTIF()` with `AVERAGE()` (`=AVERAGE(UNIQUE(A1:A8))`), but that works.

Is this expected behavior?

The problem is UNIQUE returns an array and COUNTIFS does not work with arrays.

Filter first and use COUNT:

``````=COUNT(UNIQUE(FILTER(A1:A8,A1:A8>=3)))
`````` What is interesting is if you use helper cells and spill `UNIQUE` then refer to that it works. `B1` = `=UNIQUE(A1:A8)` and `C1` = `=COUNTIF(B1#,">=3")` This works because the formula creates a `Spill Range` that the `B1#` refers to. It is like a named range.

:Caveat:

This method works with any version of Excel: 1. An Array (CSE) Formula in cell `I55`:

``````{=SUM(--(FREQUENCY(IF(G55:G62>=3,G55:G62),G55:G62)>0))}
``````
2. Finish formula with Ctrl+Shift+Enter.

How it works:

• `FREQUENCY` returns an array of values that corresponds to filtered set of data for the data array.

• The `IF` function filters data.

`IF(G55:G62>=3,G55:G62`

• Then each of these values tested to be `greater than or equals to 3`, and looks like this.

`{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}`

• `SUM` adds these values & returns the total, is `3`.
• This formula can be extended to handle multiple criteria.

``````{=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0))}
``````

N.B.

Adjust cell references in the formula as needed.

Tagged : /

## Linux HowTo: How to seperate information from one row in excel

I have a list of positions + company names. Example: “senior manager at xxx company”.

I need to separate the information that comes after the “at”, so I can get only the company name. Which formula can I use to do that?

Ana

``````B1 = left(A1,find(" at ",A1)-1)
C1 = mid(A1,find(" at ",A1)+3,len(A1))
``````

Assuming the data is in cell A1, you can enter the following formula in cell B1：

``````=MID(A1,FIND("at",A1)+2,99)
`````` if you are not a big fan of formulae enter the first company name manually in the adjacent column here cell B2 and drag as you would while filling a series but this time just click flash fill instead of the default fill series

Screenprint is available for your reference. This option works with Excel 2013 onwards.

Flash Fill

Final Output after Flash Fill

Final Output

Tagged : /

## Making Game: How to seperate information from one row in excel

I have a list of positions + company names. Example: “senior manager at xxx company”.

I need to separate the information that comes after the “at”, so I can get only the company name. Which formula can I use to do that?

Ana

``````B1 = left(A1,find(" at ",A1)-1)
C1 = mid(A1,find(" at ",A1)+3,len(A1))
``````

Assuming the data is in cell A1, you can enter the following formula in cell B1：

``````=MID(A1,FIND("at",A1)+2,99)
`````` if you are not a big fan of formulae enter the first company name manually in the adjacent column here cell B2 and drag as you would while filling a series but this time just click flash fill instead of the default fill series

Screenprint is available for your reference. This option works with Excel 2013 onwards.

Flash Fill

Final Output after Flash Fill

Final Output

Tagged : /