LAST AND UNIQUE VALUE

No Comments
Hey Guys!!!

Here's are some questions related to data and must read this post if all your answers will take to next question :-

  • Have you ever require to get filter Last and unique values from a data ? 
  • If yes what would you like to do then ? 
  • Do you do it manually ? 
  • But if it is in whole data and regular job then what would you do?

Here's a example in which you can find last and unique value from a data table using simple formulas and take decision based upon it. See how it is being done:-





You can learn the formulas used and described in the picture with there details and limitation by clicking on the following :-

  1. IF
  2. COUNTIF
  3. ""

SUM FOR FIRST AND UNIQUE VALUE

1 comment
Hey Guys!!!

Here's are some questions related to data and must read this post if all your answers will take to next question :-

  • Have you ever require to get filter first and unique values from a data ? 
  • If yes have you ever need to find total for those value ?
  • If yes what would you like to do then ? 
  • Do you do it manually ? 
  • But if it is in whole data and regular job then what would you do?

Here's a example in which you can easily find sum for first and unique value from a data table using simple formulas. See how it is being done:-

You can learn the formulas used and described in the picture with there details and limitation by clicking on the following :-
  1. MATCH
  2. IF
  3. SUMIF
  4. ""

FIRST AND UNIQUE VALUE

No Comments

Hey Guys!!!

Here's are some questions related to data and must read this post if all your answers will take to next question :-

  • Have you ever require to get filter first and unique values from a data ? 
  • If yes what would you like to do then ? 
  • Do you do it manually ? 
  • But if it is in whole data and regular job then what would you do?



Here's a example in which you can find first and unique value from a data table using simple formulas and take decision based upon it. See how it is being done:-


You can learn the formulas used and described in the picture with there details and limitation by clicking on the following :-


  1. MATCH
  2. IF
  3. ""

BREAK and COMBINE Name's

No Comments
Hey Guys!!!

Perhaps some time you find that you have to break name into Prefix, First Name, Middle Name and Last Name or you have to combine all these to get Name.If it is one time then you can do it manually. But if it is in whole data then what would you do?

Here's a example in which you can break or combine a name fields using formulas. See how it is being done:-



CONVERT DATE FORMAT

No Comments
Hey Guys!!!

Perhaps some time you find that you have to convert the date format as shown below:-

24/02/2018  >>>>>>>  24th February, 2018. 

and If it is one time then you can do it manually. But if it is in whole data then what would you do?

Here's a example in which you can change it using formulas. See how it is being done


DATEVALUE

DATEVALUE

No Comments

 “DATEVALUE”

Used for – “DATEVALUE” function in MS Excel is used to find or convert a date into a serial number that MS Excel recognizes as a date.
Syntax – DATEVALUE(date_text)

             date_text – It is any date/reference cell that contains date, you typed or calculated which you want to convert a number. It is a compulsory field and if you did not fill any value excel will not proceed further.
                       
Example – Following example will clear the DATEVALUE function:-

In below table column A contains some dates in different formats and column G contains the DATEVALUE function.

A
B
C
D
E
F
G
1







2
=DATEVALUE("01/01/2018")






3
=DATEVALUE("01-JAN-2018")






4
=DATEVALUE("01-JAN")






5
=DATEVALUE("2018/01/01")






6
=DATEVALUE(B6&"/"&C6&"/"&D6)
1
1
2018



7








And result you get as under:-

A
B
C
D
E
F
G
H
I
1









2
43101








3
43101








4
42736








5
43101








6
43101
1
1
2018





7










NOTE :- Please do care of following points when you use DATEVALUE :-
  • Date_text must be entered in double quotation i.e. “”. otherwise it will give #VALUE! error.
  • Do not mark any cell whose value is already in date format it will return #VALUE! error.
  • When we used only date and month system will consider current year based on the system date. See result of A4 cell. It consider the year 2017 as the system year is 2017.
AND

AND

No Comments

“AND”

Used for – “AND” function in MS Excel is used to check that all of the logical test/condition specified are true. This results in TRUE or FALSE which can be used as a condition for further decision.

Syntax – AND(logical1, [logical2], …)

             logical1 – It is any formula/logical test/condition or a reference cell containing result of any logical test in true or false. It is a compulsory field and if you did not fill any value excel will not proceed further.
            [logical2] – This is an optional field it also contains the logical test likewise logical1.
           
Example – Following example will clear the OR function: -

In below table column A, B, & C contains some sales data for particular item in specified cities. Now column G & H contains the OR function with different criteria.

A
B
C
D
E
F
G
1
City
ITEM
Sale
Qty.



2
New Delhi
Orange
1000
1500

=AND(C2>3000,D2<1250)
=AND(C2<3000,D2>1250)
3
New Delhi
Banana
2000
1250

=AND(C3>3000,D3<1250)
=AND(C3<2000,D3>1250)
4
Muktsar
Papaya
3000
1000

=AND(C4>3000,D4<1250)
=AND(C4<2000,D4>1250)
5
Muktsar
Orange
4000
750

=AND(C5>3000,D5<1250)
=AND(C5<2000,D5>1250)
6
Chandigarh
Banana
5000
500

=AND(C6>3000,D6<1250)
=AND(C6<2000,D6>1250)
7
Chandigarh
Papaya
6000
250

=AND(C7>3000,D7<1250)
=AND(C7<2000,D7>1250)

And result you get as under: -

A
B
C
D
E
F
G
1
City
ITEM
Sale
Qty.



2
New Delhi
Orange
1000
1500

FALSE
TRUE
3
New Delhi
Banana
2000
1250

FALSE
FALSE
4
Muktsar
Papaya
3000
1000

FALSE
FALSE
5
Muktsar
Orange
4000
750

TRUE
FALSE
6
Chandigarh
Banana
5000
500

TRUE
FALSE
7
Chandigarh
Papaya
6000
250

TRUE
FALSE

Now you can use this result as input for any formula and get result when anyone of the condition is satisfy your specified criteria.
NOTE: - Please do care of following points when you use AND: -
  • All the logical provided in the function OR must end with TRUE or FALSE.
  • Any logical which end up with empty or blank or anything other than TRUE or FALSE will get ignored.
  • If there is no logical ends up with TRUE / FALSE then function will return the #VALUE! error.
  • You can enter up to 255 logical at max.

Is this article helpful to you?

Amazon