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 ...

HOW TO GET MONEY FROM MICROSOFT EXCEL (1)

Hi,

Maybe you think is it possible to get money from Microsoft Excel? I can say,.it can be. And it would be easier if we expert in Microsoft Excel.

I devide into 2 criteria how to use their knowledge in Microsoft Excel :
1. the unemployee
2. employee

1. For those are unemployee or wish to be unemployee (he..he..he..), you can open Microsoft Excel private course by learning all the materials I made in this blog.

Why I say that? Because today, called Globalisation ... , in office there is alot of works to be done, also getting more and more complex, so officer (employee that works in office) need a simple and flexible that can be used to make their works done fast.

And refer to my experience, Microsoft Excel is very helpfull to make my works done. This program can be adjusted into the operating system of the company, so that I can make my works fast and reduce the stress.

Nowadays by using Microsoft Excel, I can make "Excel Robot" (private assistant) which work itself, with high and fast accuracy. I only give data and instruct it to solve them.

You don't have to believe it before you try it yourself. You can try it by running Macro function in Microsoft Excel. And later I will explain this function.

So just believe that you will not be regret open this private course, and not only officer but the student will become your target market.

2. For those are employee, you can only learn the syllabus in this blog, then apply it to your work, so that your work will be easier to do, you become loved more by your boss, and finally your salary and position will be raised. he..he,,

Or you can open private course after office hour. At last but not the least, i can only give you advice :

The most worthy asset is knowledge.

To be continued.....

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 ...

MY PROFILE

Refers to my birth certificate, i was born, firstly alive in this world, on June 3, 1981, exactly in wednesday night (according to my mom), that's why i called "Night Boy", but i can't really stay awake all the night...Taufiq was the name given by my father, but he told me that my grandfather want Johan as my name.



I lived and have education in this my loved country, Indonesia,.in South of Sumatera Island, Medan. I graduated from Yayasan Pendidikan Harapan Medan (YASPENDHAR),.from my playgroup to senior high school class,..cos I couldn't enter other school..hhhhmmm,.but I'm not a dumb boy.


And thanks God, I didn't spent my study to have my Bachelor in Accounting in YASPENDHAR, but in UNIVERSITAS SUMATERA UTARA (USU)..hehehe.. Now then..till this day. I have been working in PT Bank Sumut (years ago called Bank Pembangunan Daerah Sumatera Utara-BPDSU), Aksara Medan Branch..

that's all my profile,.surely till now..

Warm regards

Read More ...