CONSOLIDATE SOME WORDS

In Microsfot Excel, sometimes we not only play with amounts but with words too, like in Microsoft Word. And the result is a little bad compared if we create in Microsoft Word.

To make the result in a good order, between 2 words or more, so we use CONCATENATE formula ---> functioning to consolidate 2 words or more to be a word or sentence.

The formula is "=CONCATENATE(TEXT1,TEXT2, ….. )"

Example :

1. in coloumn A1 write " budi's sister went to the market together with her school mates"

In coloumn A3 write " and in the evening they went to swim by bus"

So in coloumn A9 write formula =CONCATENATE(A1,” “,A3)

Notes : sing “ “ used for replacing space


the result become in coloumn A9 : " budi's sister went to the market together with her school mates and in the evening they went to swim by bus.”

after consolidating, the result become a long sentence so that when we want to print it, it will over the sheets. To make it not over the sheet, more tidy by using below step :

in coloumn A9 till I11 sorted then click "merge and center".

But, after consolidating usually the text become down in the cell, so in coloumn A9 right click your mouse choose "cells a alignment --> horizontal choose justify then in vertical choose top then ok.




2. And how is it become if we want consolidate above words with amounts. Above example added :

in coloumn A5 write "and the fare per person was"

in coloumn A7 write "20000"

So in coloumn A9 =concatenate(A1,” “,A3,” “,A5,” “,TEXT(A7,"Rp, 00,00.00"))


The result will be :
“budi's sister went to the market together with her school mates and in the evening they went to swim by bus and the fare per person was Rp, 20,000".

Ok,.see you again..

Warm regards

Read More ...

EDATE FORMULA

In our daily tasks, sometimes our boss want we to calculate the due date an investment refers to the long term of the investment by using EDATE formula.

The formula is “EDATE = (start date, months)”

Example :
An obligation purchased on October 01, 2008 with the longterm 10 months, so the due date will be :
in coloumn A1 write "date of purchase", then in coloumn C1 write 01 October 2008
In coloumn A3 write "Longterm" then in coloumn C3 write 10
In column A5 write "Summary", then in coloumn C5 write "=EDATE(C1,C3)"


To get the summary in date, so right click your mouse in "summary" coloumn choose format cell in menu


then choose date in sub category, choose date format you want, and then ok.




ok,.that's all.

Read More ...

YEARFRAC FORMULA

Lets Start Again..

“YEARFRAC” formula is used to count assets aging in certain periode.
The Formula : “=YEARFRAC(START DATE,END DATE)” ----> a

E.g :

1. a buidling built on October 01,1980, then on October 01,2008 management want to know how old the building is, so u can find it by this following :



2. And how old the building on September 01,2008?
- If u use above mentioned formula, the result will be in decimal 27.91666


so how can we make it not to be in decimal?
-we can make it by adding “=ROUND()” before the formula.
to be : “=ROUND(YEARFRAC(START DATE,END DATE),0)” ----> b
This formula will make the up-rounding or down-rounding, based on our needs.
okey,.we try as per below :

It's easy, right?

3. The problem is how we know how old the building, counted in months, on September 01,2008.
So we count it by multiplying 12 months ( x 12 ) above formula ( b )
to be : “=ROUND(YEARFRAC(START DATE,END DATE)*12,0)” ---> c


4. We can also find the age, counted in days, on September 01,2008.
by multiplying 365 days ( x 365 ) above formula ( b )
to be : “=ROUND(YEARFRAC(START DATE,END DATE)*365,0)” ---> d


warm regards

Read More ...

LEARN MICROSOFT EXCEL - PROLOGUE

Working in this world nowadays, especially in Banking like me, I always work in amounts detailed, and I use Microsoft Excel to solve my job.This running job, day after day, remains the same and more jobs to be done. And sometimes make me bored and have a little stress,I guess.

Here, I would like to share a little about Microsoft Excel to make your job to be easeier to do. but, I never teach you how to open a file, copy, make a chart or else. I only share my knowledge in Microsoft Excel : How to use some formulas to solve your job in office.

Ok,.let's start :)

I use Microsoft Excel 2003, the formulas will be learned should be activated in Microsoft Excel, because usually, in Microsoft Excel still show standar formulas.

To activated the formulas is :
1. open Microsoft Excel
2. choose Tools
3. then select Add-ins

4. mark Analysis Toolpak and Analysis Toolpak - VBA

if there is pop-up to update, please take update file from Microsoft Office CD installer.

Ok,. that's it for now

Warm Regards

Read More ...