Excel Assignments

Akash Deep Nov 15 2021 · 3 min read
Share this

Assignment 1

1.      Convert to Date Format (Convert-to-Date-Format.xlsx)

For this exercise we will use the DATE function.

DATE (year,month,day)

DATE function requires 3 arguments DAY, MONTH, YEAR and each of these has to be a number rather than text.

The month, year and date is the same in all cells. Month = January = 1; Year = 2017 and Day = 2nd

Once we enter the value in the first cell. Click on the cell and double click at the right down corner of the cell to automatically fill the rest of the column.

Assignment  2:

We need to fill the blanks in PIC 1 using COUNTIF/COUNTIFS and SUMIF/SUMIFS.

COUNTIF is used for counting cells with a single condition in one range.

COUNTIFS evaluate different criteria in the same or in different ranges. 

number of microwave orders  : =COUNTIF(D2:D25,"microwave")

number of journeys with truck 3: =COUNTIF(F2:F25,"truck 3")

number of Peter White journeys: =COUNTIF(C2:C25,"Peter White" )

how many times are no. of items less than 20:

=COUNTIF(E2:E25," < 20")

SUMIFS function sums cells in a range using supplied criteria.

SUMIFS can apply more than one set of criteria, with more than one range.

sum of refrigerator items:  =SUMIF(D2:D25,"refrigerator",E2:E25)

sum of washing machine items: = SUMIF(D2:D25,"washing machine",E2:E25)

sum of items transported by truck 4:=SUMIF(F2:F25,"truck 4",E2:E25)

sum of items transported by trucks: =SUMIF(F2:H39F25,"truck*",E2:E25)

The next exercise was done using the COUNTIF/COUNTIFS and SUMIF/SUMIFS formulas as in the below table:

Assignment 3 – Find duplicates and show the count.

In another column, use this formula to extract the common terms;

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%"))," ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

Add this formula in the count column:

=IF(COUNTIF($E$1:E2,E2)=1,COUNTIF(E:E,E2),"")

Assignment 4 – Find the position of the word

Using the below formula, we can find the position of a word.

=SEARCH($B$2,A2)-LEN(SUBSTITUTE(LEFT(A2,SEARCH($B$2,A2))," ",""))+1

Assignment 5 – Generate Multiplication Table.

The given excel file is as below:

We will use the multiplication formula =$A2 +B$1 in cell B2

To create the table, we follow the below steps:

  • Select cell B2 and click on it
  • Insert the formula: =$A2 + B$1 or $A2*B$1
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

    • Drag the formula down to the other cells in the rows by clicking and dragging the little “+” icon at the bottom-right of the cell.

    Assignment 6:

    Sumifs , sumproducts and pivot table

    =SUMPRODUCT($C$2:$C$150000*($B$2:$B$150000=$E3)*($A$2:$A$150000=F$2))

    for sumproduct:

    formula:=SUMPRODUCT(($C$2:$C$150000)*($B$2:$B$150000=$E3)*($A$2:$A$150000=F$2))

    Pivot table:

    Assignment 7:

    Solving the palindrome 

    =IF(SUMPRODUCT(--(MID(SUBSTITUTE(B7," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B7," ","")))),1)=MID(SUBSTITUTE(B7," ",""),LEN(SUBSTITUTE(B7," ",""))+1-ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B7," ","")))),1)))=LEN(SUBSTITUTE(B7," ","")),"Palindrome","Not Palindrome")

    Assignment 8:

    problem workbook

    Assignment 9:

    Calculating the amounts of mean,count,sum,min,max,std.

    =SUM(A2:A41)

    =COUNT(A2:A41)

    =MAX(A2:A41)

    =MIN(A2:A41)

    =STDEV.P(A2:A41)

    Assignment 10

    Sum for merged cells

    * * *

    https://github.com/akashdeep364/Excel-Assignment

    Comments
    Read next