Sunday, 15 November 2015

Blog 53 - (Abs) Retrieve Absolute Value

Today's topic is ABS function. We will learn how it works and what the argument is required.


ABS function helps user to retrieve the absolute value from and positive or negative numbers. But before that we need to understand what absolute number is. Just look that below picture might you will go to your childhood.





Absolute value describes the distance of a number on the number line from 0 without considering which direction from zero the number lies. The absolute value of a number is never negative.

ABS function require only one argument and that is number.

=ABS(number)





Keep Learning
Sanjay Bakshi
mesanjaybakshi@gmail.com

Tuesday, 10 November 2015

Blog No - 52 - FunctionS in Excel (Days360) & Happy Diwali

Today's topic is Days360 function. We will learn how it works and what the argument is require.

Days360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

Days360 require three arguments

1)      Start Date
2)      End Date
3)      Method (This is an optional and logical value which specifies whether to US or Europe method in calculation. False means US and True means Europe)






Keep Learning & Happy Diwali
Sanjay Bakshi
mesanjaybakshi@gmail.com





Saturday, 7 November 2015

Blog No 51 - FunctionS in Excel (Day)

In today's topic we will discuss about day function and we will learn how it works.

Day functions returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

=day(Serial No)

Day function require one arguments which is a serial no which is basically a date. Why it is mentioned a serial no, because Excel treat date as a serial no in spreadsheet.





Keep Learning
Sanjay Bakshi
mesanjaybakshi@gmail.com



Monday, 2 November 2015

(2nd Nov 2015) - FunctionS in Excel (Datedif)

   In today’s working  environment it is very much important to learn       about Datedif functions. This function basically calculates the number of days, months or years between two dates.
   The DATEDIF function can be used to calculate the time period or difference between two dates. This time period can be calculated as the number of days, months, or years between the two dates.
    
   Uses for this function include planning or writing proposals to determine the time frame for an upcoming project. It can also be used, along with their birth date, to calculate a person's age in years, months, and days.
    This function is an undocumented function which means it is not     listed with  other date functions. 



   The syntax for the DATEDIF function is:
   = DATEDIF ( start_date , end_date , "interval")
    
   •Interval tells the function to find
D - Returns the number of days in the period.
M - Returns the period difference as complete months.
Y - Returns the period difference as complete years.
MD - Returns the difference between the days in ‘Start_Date’ and ‘End_Date’.Here the months and years of the dates are ignored
YD - Returns the difference between the months in ‘Start_Date’ and ‘End_Date’. Here the days and years of the dates are ignored.
YM - Returns the difference between the days of ‘Start_Date’ and ‘End_Date’. Here the years of the dates are ignored.

How to use datedif formula



Some Examples : Calculate no of months completed in service




Some Examples : Calculate Age in text format




Some Examples : Calculate no of day's left



Validation Required

Start date should not be greater than End Date
Arguments should not invalid
Use correct unit name



Keep Learning
Sanjay Bakshi
mesanjaybakshi@gmail.com

Sunday, 1 November 2015

Functions in Excel =date()

Functions in Excel 

=date()

     When we work in Excel it is very important to understand how date calculations work. Date functions plays a vital role in this. For users excel display the date in understandable format but actually excel treat dates as a number while processing.
       
       Some time in development environment clients will send the in notepad and you need to import that data into excel with date a text format, in those situation we can use the =date() functions and get the accurate date.
      
       Excel stores dates as a serial no and to know about that serial no we need to use =date() function.
    

      Date functions uses Year, Month, Day as an argument.



Get the serial no of date




Get the date from text value





Date is the main function to work with dates in excel but there are lot of functions are available. In coming days we will go one by one with these functions.

Keep Learning
Sanjay Bakshi
   mesanjaybakshi@gmail.com



Saturday, 12 September 2015

Track Changes in Excel

In today’s environment you will come across with many instances where you have to share your workbook to get used by other multiple users. Now question comes what if they have done any change how will you know as an administrator. You might want to track when the change was made, who made the change, what cell the change occurred in and what data was changed.

This article will help you to do get a solution for above problem.

Excel is having a built in feature which can help us to track the changes in excel file. We can easily review the changes on the worksheet and we can even accept and reject the changes.

There are couple of rules which we need to follow while using above feature

        1)    We cannot revert the worksheet to its original state. If you want to do the same check the tracking record    and change the value manually.
        2)    Tracking changes just record the data changes not the formatting changes
        3)    By default history will be available for 30 days.
        4)    As soon we will start the tracking, workbook will become shared workbook.

Here the steps which we can follow

Create One Excel File


Click Review Tab

Click Track Changes

Click Highlight Changes



Click on Track Changes While editing

Select information for When,Where and Who

Provide Cell No in reference for which you want to tracking to enabled


That’s it, now whenever anybody will change the values, cell will highlight and if you move cursor to that


We have the option to track the changes also. We can generate the summary report by following below steps

Click on Review

Click in Share Workbook


Go to advanced tab, change the days and click ok



Select the highlight cell

Click on Track Changes

Select highlight changes

Select checkbox of “List Changes into new sheet”



That’s it your changes history will be available in new sheet like below


At some point you’ll need to either approve or reject the changes. You can review all the changes and then pick and choose which ones you want to keep or discard.
Simply click on Track Changes again and choose Accept/Reject Changes. Select the options to choose which changes you want to accept or reject. If you want to review all changes, just leave When checked and make sure it is set to Not Reviewed.

Click OK and Excel will begin to show you each change that was made and give you the option to Accept orReject. You can also Accept All or Reject All changes if you like.

Click on Review

Select Track Changes

Select Highlight Changes
Accept/Reject based on situation


Hope this feature will help you in many ways..


Thanks,
Sanjay Bakshi
mesanjaybakshi@gmail.com

Friday, 4 September 2015

Cloud Computing for Beginners



Cloud Computing is a model for enabling network access as a shared pool. This concept provides clients to store their data to third party data centre. It relies on sharing of resource over a network. It maximizes the effective use of shared resources. Resources available in cloud are not only used by multiple users but allocation happen based on demand. This approach helps clients and companies to reduce their cost.

When you read that a particular company is moving to cloud that means that they are not going to purchase any hardware/software or any technical CAPEX item and will the take those services via cloud.

There are many benefits we get as a company once we move to cloud.
  • Data Centralization
  • Cost Reductions
  • Resource sharing
  • Continuous Monitoring
  • Easy Maintenance
  • Reliability

Large companies which wanted to move to cloud they need to finalize that which kind of services theywanted to use.
  •   IAAS (Infrastructure as a Service)
  •   PAAS (Platform as a Service)
  •   SAAS (Software as a service)
        IAAS
        If we go with Infrastructure as a service we get virtual machines, servers, storage and network

        PAAS
       If we go with platform as a service, we get a computing platform which includes OS, Environment for programming, database and servers. Software developer can built the code and execute

        SAAS
       If we go with software as a service then we can get the access of application software and databases. We can go with software and even can pay based on usage.

       Once we finalize what kind of services we are going to use we decide on deployment. Basically there are many deployment methods are available but three kind of deployment are very common  

  •            Private Cloud
  •            Public Cloud
  •            Hybrid Cloud

         Private Cloud
         This deployment will be for a single organization, owner can decide whether that will be managed by company    people or nay third party. Once you feel like going on private cloud you need to decide what you are going to      do with your current infrastructure. This requires significant level of changes and this will be costly also.

         Public Cloud
         Public cloud services may be free and you can opt all the services which are open for public use. Services        which we used via public cloud go via non trusted network.

       Hybrid Cloud
       Hybrid cloud is a composition of two or more clouds (Private, Public or any other). System treats these different entities but will be bound together. This deployment helps system manager to identify services based on actual requirement. Hybrid cloud is one where IT organizations use public cloud computing resources to meet temporary capacity needs that cannot be met by the private cloud.
      
      Having said that there are some privacy and security issues which comes in the picture like
      Cloud computing poses privacy concerns because the service provider can access the data that is on the cloud at any time. It could accidentally or deliberately alter or even delete information. Many cloud providers can share information with third parties if necessary for purposes of law and order even without a warrant. Users can encrypt data that is processed or stored within the cloud to prevent unauthorized access.


      Keep Reading
      mesanjaybakshi@gmail.com