C RUBY-ON-RAILS MYSQL ASP.NET DEVELOPMENT RUBY .NET LINUX SQL-SERVER REGEX WINDOWS ALGORITHM ECLIPSE VISUAL-STUDIO STRING SVN PERFORMANCE APACHE-FLEX UNIT-TESTING SECURITY LINQ UNIX MATH EMAIL OOP LANGUAGE-AGNOSTIC VB6 MSBUILD

# Creating hierarchical sequence in Excel using several functions in formula

By : treasure ngaobiwu
Date : November 21 2020, 01:01 AM
With these it helps Ok, first of check this article out here, you should be able to find everything you need to create what you need. Now here is all formulas per every level in your example
Level 1
code :
``````=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A1,-1,0,1,1),".",""))),1,IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))),VALUE(OFFSET(A10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(A1,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))-1))+1))
``````
``````=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A2,-1,0,1,1),".",""))),"0.1",IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))),OFFSET(A2,-1,0,1,1)&".1",LEFT(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1)))&IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))),VALUE(RIGHT(OFFSET(A2,-1,0,1,1),LEN(OFFSET(A2,-1,0,1,1))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))))+1,VALUE(MID(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))+1,(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))-1)))+1)))
``````
``````=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A3,-1,0,1,1),".",""))),"0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))),OFFSET(A3,-1,0,1,1)&".1",LEFT(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))),VALUE(RIGHT(OFFSET(A3,-1,0,1,1),LEN(OFFSET(A3,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))))+1,VALUE(MID(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))+1,(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))-1)))+1)))
``````
``````=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A4,-1,0,1,1),".",""))),"0.0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))),OFFSET(A4,-1,0,1,1)&".1",LEFT(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))),VALUE(RIGHT(OFFSET(A4,-1,0,1,1),LEN(OFFSET(A4,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))))+1,VALUE(MID(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))+1,(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))-1)))+1)))
``````

Share :

## Using Excel Formula functions (ERF, ERFC) in Excel VBA code?

By : Omneya Atef
Date : March 29 2020, 07:55 AM
help you fix your problem To use a worksheet formula in vba, you need to put Application.WorksheetFunction. in front of it.
Some functions do have vba equivalents, but (as far as I know) not in the case of erf and erfc

## Creating a hierarchical structure from a list of sequence strings

By : Maryam Hammami
Date : March 29 2020, 07:55 AM
Hope that helps It would help a bit to add Sequence property to the class and use it to find the parent:
code :
``````public class Hierarchy
{
public int ID { get; set; }
public int ParentID { get; set; }
public string Sequence { get; set; }
}

public static IList<Hierarchy> GetHierarchy(IList<string> sequenceList)
{
int iD = 0;
List<Hierarchy> hierarchy = new List<Hierarchy>();

foreach (string sequence in sequenceList)
{
iD++;
List<string> childSequence = new List<string>();

string[] sequenceParts = sequence.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
// If the sequence contains sub-sequence, i.e. "2.1" is a sub-sequence of "2".
if (sequenceParts.Count() > 1)
{
var parentSequence = sequence.Substring(0, sequence.LastIndexOf("."));
var parent = hierarchy.Single(x => x.Sequence == parentSequence);

hierarchy.Add(new Hierarchy() { ID = iD, ParentID = parent.ID, Sequence = sequence });
}
else
hierarchy.Add(new Hierarchy() { ID = iD, ParentID = 0, Sequence = sequence });
}

return (IList<Hierarchy>)hierarchy;
}
``````
``````public class Hierarchy
{
public int ID { get; set; }
public int ParentID { get; set; }
}

public static IList<Hierarchy> GetHierarchy(IList<string> sequenceList)
{
int iD = 0;
List<Hierarchy> hierarchy = new List<Hierarchy>();

foreach (string sequence in sequenceList.Where(x => x.Count(f => f == '.') == 0)) // get the root nodes
{
iD++;

var item = new Hierarchy() { ID = iD, ParentID = 0 };
hierarchy.AddRange(GetChildsRecursive(sequence, item.ID, sequenceList, () => ++iD));
}

return (IList<Hierarchy>)hierarchy;
}

private static IList<Hierarchy> GetChildsRecursive(string parentSequence, int parentId, IList<string> sequences, Func<int> idGenerator)
{
var parentDots = parentSequence.Count(f => f == '.');
var childSequences = sequences.Where(x => x.StartsWith(parentSequence) && x.Count(f => f == '.') == parentDots + 1);

var list = new List<Hierarchy>();
foreach (var childSequence in childSequences)
{
var item = new Hierarchy() { ID = idGenerator(), ParentID = parentId };
}
return list;
}
``````

## Creating recurrent sequence by conditional formula in F#

By : mark
Date : March 29 2020, 07:55 AM
Hope that helps You could do it using a mutable value and a for loop. Alternatively, it is possible to use unfold:
code :
``````Seq.unfold (fun (a,i) ->
let a' =
if i % 2 = 0
then a*2
else a+1
Some (a', (a',i+1)))
(1,1)
|> Seq.append [1]
``````

## Is there a way to record down the sequence of Excel formula calculation

By : daimatz
Date : March 29 2020, 07:55 AM
hop of those help? To determine the last calculation sequence used you could extract the calculation chain part from the XLSX/XLSM file and decode it. See https://msdn.microsoft.com/en-us/library/office/gg278336.aspx as a starting point
For small scale tests you can use a Calculation Sequence tracking UDF like this one

## Excel Formula Series of #'s and Sequence

By : user3658149
Date : March 29 2020, 07:55 AM
This might help you I need a formula that uses a series of numbers in one column (numbers 1-21) and returns just a number for every 21 cells. , Put this in the first column first cell:
code :
``````=MOD(ROW(A1)-1,21)+1
``````
``````=INT((ROW(A1)-1)/21)+1
``````