Age

This formula will you to find age of a person or in reporting area to find age of an asset, liability, deposits &...

=INT(YEARFRAC(StartDate,TODAY()))

Int()= Int is a formula to roundoff the number to the lower side.

Yearfrac()= Yearfrac is a formula to calculate difference in years from StartDate to EndDate.

Today()= Today is a formula to get today's date which is used in this formula as end date, so that we can get age uptil today.
              

Sumifs()

This formula helps to sumup based on multiple criteria. It is same as sumif but sumif support only one condition whereas sumif support multiple conditions

=SUMIFS(sumrange, criteriarange1, criteria1,criteriarange2, criteria2, criteriarange3, criteria3, ....)

In example in the screenshot above the sumifs is calculating based on two criteria first is invoice number and the second is product type.

Count by color

This addin create a formula called countbycolor that helps to count all the values in same Color you select as criteria.

=Countbycolor(Criteria,Count range)

Criteria- The cell with the Color you want to count.

Count Range= Is the range of the for which you want the total count.

Click here to download addin

Sum by Color

This addin create a formula called sumbycolor that helps to sum up all the values in same Color you select as criteria.

=Sumbycolor(Criteria,Sum range)

Criteria- The cell with the Color you want to sum up.

Sum Range= Is the range of the for which you want the total.

Click here to download addin


To add the addin go to

File-Options-addins-GO(at the bottom)-browse-paste the addin-double click on it-ok.

Hyperion Retrieve by Macro

Declare function essmenuvconnect lib "essexcln.xll" () as long

Declare function essvconnect lib "essexcln.xll" (byval sheetname as variant, byval username as variant, byval password as variant, byval server as variant, byval application as variant, byval database as variant) as long

Sub hype()

' To connect to essbase cube
G= essvconnect(sheetname,username, password,server name, application name, database name)

' To Retrieve data
Selection.application.run macro:="essmenuretrieve"

End sub

The details about server name and other you will get in login pop-up of essbase.

Indirect()

This formula is used to get a cell value for cell name referred in a cell. This formula can be used in conditional formatting and with other formulas.

=INDIRECT(CELL NO, A1 Style/ R1C1)

Cell No= Cell with cell Name.
A1 style= Normal cell referencing
R1C1= One we use in macro. R is row & C is column. Here R then row number , C then Column Number.

Defining variable value by excel formula

Application.Worksheetfunction.formulaname

This will help while defining loops. A variable can be defined for the number of times loop should run. This can be done by defining formula to variable.

Application Screen Updating

This Macro code will make macro run faster by avoiding displaying the background view of running macro. You will not see anything happening in the background while the macro is running.

Sub xyz()

Application.screenupdating =False

Your Codings

Application.screenupdating =True

End sub

OR()

OR(condition1, condition2,....)

This give output as true or false.
If one of the conditions is true then output will be true. If both the condition are false then output will be false.

You can use this with If().

AND()

=And(condition1, condition2,....)

This give output as true or false.
If both the conditions are true then output will be true. If any of the condition is false then output will be false.

You can use this with If().

Excel Shortcuts

Copy= Ctrl+C
Paste= Ctrl+V
Paste Value= Menu Key(key between Windows key & Ctrl.)+V
Paste Formula= Menu Key(key between Windows key & Ctrl.)+F
Paste Format= Menu Key(key between Windows key & Ctrl.)+R
Hyperlink= Ctrl+K
Find= Ctrl+F
Find Replace= Ctrl+H
Add/Remove total Filter= Alt+D+F+F
Remove Just Filter=Alt+D+F+S
Sort= Alt+D+S
Text to Column= Alt+D+E
Create Pivot Table= Alt+D+P
Open VB Editor= Alt+F11
Select Visible cells & Copy & Paste= Alt+; then Ctrl+C and then Ctrl+V

ABS()

This formula helps to removes sign from number.

ABS(number)

Number= Select cell with number you this formula to work.

For example: When we pull expenses from ledger it shows as negative , but for Reporting purpose we want it to be positive. In this case we can use this formula.

Countblank()

This formula helps to calculate only blank cells in given range.

=Countblank(range)

Range= Area/ Range to count

Multiple ifs

This formula is to have multiple values of Multiple conditions.

=If(criteria1,then,If(criteria2,then,If(criteria3,then,....)

Criteria: Is the logic that you want.

Then: If the Criteria or logic is true then want should be the value. Remember to add double inverted commas for text.

Else: If the Criteria or logic is false then want should be the value. Remember to add double inverted commas for text.

If()

This helps to create logic in Excel. As we say if then that.

If(criteria,then,else)

Criteria: Is the logic that you want.

Then: If the Criteria or logic is true then want should be the value. Remember to add double inverted commas for text.

Else: If the Criteria or logic is false then want should be the value. Remember to add double inverted commas for text.

Hlookup()

This formula helps to pull required data from entire data. This it just reversal of vlookup. Difference between vlookup & hlookup is Vlookup pull column wise data while hlookup pulls row wise data.

=Hlookup(lookupvalue, tablerange,row index,range lookup)

Sum()

Sum(sumrange)/

Sumrange= range/ area that you want to sumup.

Sum(Number1,Number2,....)

Sum can also be used to sum only specific cells.

number1= cell one to sum
number2= cell two to sum

Other way to sum cells

=Number1+Number2+Number3+....

Counta()

This formula helps to count only cell with values in the given range. It will exclude blank cells.

=Counta(range)

Range= Area/ Range to count

Count()

This formula will only count cells containing numbers in the given range.

Count( Range)

Range = Area where count is to be performed

Countif()

This formula will help you to Count according to condition.

=Countif(range, criteria)

Range= Area for which count is to be performed.

Criteria= What is required to count.

For ex: There is a messed data of sales.
You want total count of a particular deal. This is where you can use this formula.

Vlookup()

This formula helps to pull required data from entire data.

For example: In sales data if I want to pull  
Salesperson Name for specific deals vlookup can be used.

=Vlookup(lookupvalue, tablerange,col index,range lookup)

Lookup Value= In above example Lookup value is deal number.
Table range= Range will start from column with deal details and end with column with salespersons detail.
Col index= Column number of salesperson column in your range.
Range lookup= Keep always "0". This means exactly same

Sumif()

This formula helps you to sumup only those cell which you require based on near by column.

For ex: There is a messed data of sales.
You want total sales by sale agent xyz. This is where you can use this formula.

Formula:

Sumif(range, criteria, sumrange)

Where.
Range= Criteria Range. In the above example range containing agents name will be the criteria.

Criteria= Criteria or Condition. In the above example cell containing agents name for whom you want total sales(freeze it) or you can type the name manually in double quotes.

Sumrange= The range which you want to sumup. In the above example range containing sales numbers.