Excel Calculation

[naveen.s]

Hi All,

I want to find below things from the excel:

1. Result: if any of the subject is failed then need to write fail or else pass.
2. Grade: if the total marks is less than 360 should give Grade C, between 360 to 500 Grade B, if greater than 500 then Grade A.
3. Count of the Failed Subject: count of the failed subject if anyone scored less than 35
4. Count of the Passed Subject count of the Passed subject if anyone scored greater than 35
5. Rank: Rank should be the student scored more marks(Total should give 1 and least total should give last rank).
   StudentsDetails.xlsx (13.7 KB)

[pravallikapaluri]

Hi @naveen.s

Excel.zip (165.1 KB)

Hope it helps!!

[pravallikapaluri]

@naveen.s

Check the below workflow

[mkankatala (Mahesh Kankatala)]

Hi @naveen.s

You can use the LINQ expression and vb code to get the required output, check the below steps,
→ Use the Read Range workbook activity to read the excel and store in a datatable called dt_Input.
→ Then use the assign activity to write the LINQ Expression,

- Assign -> dt_Output = (From row In dt_Input 
                        Let StudentName = row("StudentName").ToString() 
                        Let RollNumber = row("RollNumber").ToString()
                        Let Kannada = row("Kannada").ToString() 
                        Let English = row("English").ToString() 
                        Let Hindi = row("Hindi").ToString() 
                        Let Science = row("Science").ToString() 
                        Let Maths = row("Maths").ToString() 
                        Let Social = row("Social").ToString() 
                        Let Total = row("Total").ToString() 
                        Let Result = If((CInt(Kannada) < 35 Or CInt(English) < 35 Or CInt(Hindi) < 35 Or CInt(Science) < 35 Or CInt(Maths) < 35 Or CInt(Social) < 35), "Fail", "Pass") 
                        Let Grade = If(CInt(Total) < 360, "C", If(CInt(Total) > 500, "A", "B")) 
                        Let Percentage = row("Percentage").ToString() 
                        Let ArrSubject = New String() {Kannada, English, Hindi, Science, Maths, Social} 
                        Let CountOftheFailedSubject = ArrSubject.Count(Function(X) CInt(X) < 35).ToString() 
                        Let CountofthePassedSubject = ArrSubject.Count(Function(X) CInt(X) >= 35).ToString() 
                        Let Rank = If(String.IsNullOrEmpty(row("Rank").ToString()) OrElse String.IsNullOrWhiteSpace(row("Rank").ToString()), "", "") 
                        Select dt_Input.Clone.Rows.Add({StudentName, CInt(RollNumber), CInt(Kannada), CInt(English), CInt(Hindi), CInt(Science), CInt(Maths), CInt(Social), CInt(Total), Result, Grade, CDbl(Percentage), CInt(CountOftheFailedSubject), CInt(CountofthePassedSubject), Rank}) 
                                	 ).CopyToDataTable()

→ Then use the Invoke code activity and give the below code, pass the arguments as in the image,

' Add Rank column if it does not exist
If Not dt.Columns.Contains("Rank") Then
    dt.Columns.Add("Rank", GetType(Integer))
End If

' Sort rows by Total in descending order and assign ranks
Dim sortedRows As DataRow() = dt.Select("", "Total DESC")
For i As Integer = 0 To sortedRows.Length - 1
    sortedRows(i)("Rank") = i + 1
Next

' Create a new DataTable and import sorted rows
Dim sortedDataTable As DataTable = dt.Clone()
For Each row As DataRow In sortedRows
    sortedDataTable.ImportRow(row)
Next

' Sort the DataTable by StudentName in ascending order
sortedDataTable.DefaultView.Sort = "StudentName ASC"
dt = sortedDataTable.DefaultView.ToTable()

→ Then use the write range workbook activity to write the dt_Output to same excel sheet.

Check the below workflow for better understanding,
Sequence.xaml (10.6 KB)

Hope it helps!!

[SenzoD (Senzo Dlomo)]

Hi there,

Option1

here are the formulas you can insert and autofill. the disadvantage of this option is you have to know the last cell position with data.

Results:
=IF(OR(C2<35, D2<35, E2<35, F2<35, G2<35, H2<35), "Fail", "Pass")

Grade:
=IF(I2>500, "A", IF(I2>=360, "B", "C"))

Count of the Failed Subject:
=SUM(IF(C2<35, 1, 0), IF(D2<35, 1, 0), IF(E2<35, 1, 0), IF(F2<35, 1, 0), IF(G2<35, 1, 0), IF(H2<35, 1, 0))

Count of the Passed Subject:
=SUM(IF(C2>=35, 1, 0), IF(D2>=35, 1, 0), IF(E2>=35, 1, 0), IF(F2>=35, 1, 0), IF(G2>=35, 1, 0), IF(H2>=35, 1, 0))

Rank:
=RANK.EQ(I2, $I$2:$I$10, 0)

Option2

Read your Excel data into a datatable eg dt and then use LINQ to fill the values:

–Linq-1 (calculates all excepts ranking)

From row In dt.AsEnumerable()
               Select New With {
                   .StudentName = row.Field(Of String)("StudentName"),
                   .RollNumber = row.Field(Of Integer)("RollNumber"),
                   .Result = If(row.Field(Of Integer)("Total") < 210, "Fail", "Pass"),
                   .Grade = If(row.Field(Of Integer)("Total") < 360, "C", 
                              If(row.Field(Of Integer)("Total") < 501, "B", "A")),
                   .CountOfFailedSubject = (From mark In row.ItemArray.Skip(2)
                                            Where CInt(mark) < 35 Select mark).Count(),
                   .CountOfPassedSubject = (From mark In row.ItemArray.Skip(2)
                                             Where CInt(mark) > 35 Select mark).Count(),
                   .Total = row.Field(Of Integer)("Total"),
                   .Percentage = CDec(row.Field(Of Integer)("Total")) / 600 * 100,
                   .Rank = 0 ' We'll calculate this later
               }

Linq-2 Calculates the ranking:

s

dt.Select(Function(s) s With {.Rank = 
                                          (From prev In students.TakeWhile(Function(p) p IsNot s)
                                           Select prev.Total).DefaultIfEmpty().Max() + 1}})

Now you can write range into the same sheet if you want, it will overwrite old data with new data and the calculations, this is much simpler and you do not have to know the range upfront

[naveen.s]

What does Let indicates here??

[mkankatala (Mahesh Kankatala)]

Let is used to define temporary variables that hold values computed or extracted from each row in the data source… @naveen.s

Hope you understand!!

[naveen.s]

@mkankatala

ty and giv

getting error like that

[mkankatala (Mahesh Kankatala)]

You have given the wrong expression, have you copied the total expression which I have given in the above post… @naveen.s

[naveen.s]

I got it, can you please explain more about Rank stuff??