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

Original Source Link

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.

Tagged : / /

Server Bug Fix: Incorrect values obtained through Excel formula

Original Source Link

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:
enter image description here

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

Try this instead:

=SQRT(2*B3/$B$1)

enter image description here

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

Original Source Link

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.

Tagged : / /

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

Original Source Link

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?

thank you in advance!

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

Original Source Link

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

enter image description here

Tagged : / /

Making Game: Insert line break for a specific character within a cell every time it is repeated

Original Source Link

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

enter image description here

Tagged : / /

Linux HowTo: Apparent false syntax error using UNIQUE() in COUNTIF()

Original Source Link

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

enter image description here


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

enter image description here

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:

enter image description here

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

Original Source Link

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

enter image description here


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

enter image description here

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:

enter image description here

  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

Original Source Link

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?

Many thanks in advance,
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)

enter image description here

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

Original Source Link

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?

Many thanks in advance,
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)

enter image description here

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 : /