Excel Assignments Solutions

Assignment  1

Conditional Formatting

Conditional Formatting with criteria in array

Note:-

Steps 

1. Go To Conditional Formatting tool.

2. Select the new rule....

3. Select the "use a formula to determine which cell to Format".

4. Click on "Edit the rule Description:" then type the function.

5. After that go to format and choose the fill option, border option, font editing option or number option it's all depend on you click "ok" and again press on "ok".

6. Again open conditional formatting tool and choose manage rules and you can edit your function.

Function Use

ISODD Function :- Excel Function isodd is use to return true value if a numeric value is odd. Otherwise false if numeric value is Even.

ISNUMBER Function :- You can use it to check whether a value in a cell is a numeric value entered as text. Like ISNUMBER, this function also returns either TRUE of FALSE.

ISTEXT Function :- this function is use to check whether of value in a cell is text or not . This function is also returns either True of False. 

ISERROR Function :- ISERROR is a logical function which is used to identify whether the cells being referred to has an error or not, this function identifies all the errors and if any type of error is found out in the cell it returns TRUE as result and if the cell has no errors it returns FALSE as the result, this function takes a cell reference as an argument.

For ODD Number

This Function is use to find the odd value in the range.

Syntax 

              =ISODD(Cell Address)

Function is use in criteria

            =ISODD(B3)

For Text

This function is use to find Text in the range.

Syntax

             =ISTEXT(Cell range)

Function use in criteria 

             =ISTEXT(F3:I17)

For Even Number

This Function is use to find the #N/A and Error Value in the range.

Syntax 

              =ISEVEN(Cell Address)

Function use in Criteria

            =ISEVEN(F3:I17)

For ERROR

 This Function is use to find the #N/A and Error value in the range.

Syntax 

              =ISODD(Cell Address)

Function is use in criteria

            =ISODD(B3)

Assignment  2

Conditional Formatting

Minimum and Maximum...…

Note :-

This assignment is totally based on conditional formatting

All Function are apply to conditional formatting, to apply this function follow these steps.

• Go to conditional formatting tool.
• Select the New Rule.....
• Select the :use a formula to determine which cell to format".
• Click on " Edit the Rule Description" then type the function.
• After that go to the Format and choose the options which you want. It all depend on you and click on "ok" and again click on "ok".
• Again open Conditional formatting tool and choose manage rules. then you can edit your function. 

Function Use

If Function :- If function will check entire function and give the answer true and false.

Minimum or Min Function :- This function is use to find the minimum value in the given range.

Maximum or Max Function :- This function is use to find the maximum value in the given range. 

And Function :- This function use to check multiple logics. If all logic will true then it will be print true otherwise it will be print false.

Min and Max

Syntax

Function use  in this criteria 

=If($G6=Min($G$6:$G$15),"Min", If($G6=Max($G$6=$G$15),"Max","Space"))=$C$23

• If function in this Criteria :- If function will check entire function.
• Min function in this criteria :- This is use to find the minimum value in the given range.
• Max function in this criteria :- This function is use to find the maximum value in the given range .
• Symbol of "$" :-  Dollar sign fixes the reference to a given cell.

In Between

Syntax

Function use  in this criteria 

=And($G6>=$G$23,$G6<$H$23)

• And function use in this Criteria :- And Function checks both Criteria.
• Symbol of ">" :-  Greater than is a comparison operator .Use Greater than  operators in Excel to check if one value is greater than another value. 
• Symbol of "<" :-  Less than is a comparison operator .Use Less than  operators in Excel to check if one value is Less than another value. 
• Symbol of "$" :-  Dollar sign fixes the reference to a given cell.