-
Part-1
Introduction:
Microsoft Excel is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993. Excel forms part of Microsoft Office. The current versions are 2010 for Windows and 2011 for Mac.
The number of row of Microsoft Excel is 65,536 and the number of column is 256.
MS Excel : Microsoft Excel Is a Great Software for our Life. Personal Accounting, Financial Accounting, Official works, Variety of Calculation can be easily done by MS Excel.
Click for download (http://www.ziddu.com/download/12335205/GettingStartedwithMicrosoftExcel.doc.html)
Source:http://forum.daffodilvarsity.edu.bd/index.php/topic,2691.0.html
I have copied the source file from Mr. Rubel sir. Mr. Rubel sir has described about Ms Excel very nicely and his attached file is really very informative. So, you can know from the attached file the basic operation of Excel.
However, I will share with you the different functions of excel.
-
(http://i56.tinypic.com/303gvut.jpg)
All of these are the input value.
(http://i55.tinypic.com/nb7iti.jpg)
(the above function as you can see some "." (dot), when you type the formula, Please ignore the dot. Otherwise it will not work.
A6, B6, C6, D6, E6, F6, G6, H6 are the output value.
I try to find out the sum in the A, B, C, D and E no column.
Ex: First of all, type the input value like image shown after that put the mouse cursor on the A6 no cell and type the formula like =SUM(A3:A5). You will get the desire value.
N.B: The formula of Sum1 and Sum2 are the same. Both are working. However, always try to use first one. Because it saves your time and more flexible.
As you can see, I try to find our Column no F is average like (4+3+2)/3=3. type the following formula on the cell no F6.
=AVERAGE(F3:F5)
MIN means minimum. Using this formula will find out the minimum value.Such as 3, 6, 4, Here the minimum input value is 3.
MAX means Maximum value. Using this formula will find out the Maximum value. Such as 9, 5, 6, Here the maximum input value is 9
-
(http://i52.tinypic.com/2mn4mb.jpg)
If you want, you can download soft copy.
Click Here (http://www.ziddu.com/download/12349141/Ashiq.xls.html)
-
(http://i55.tinypic.com/o53vp1.jpg)
N.B: Avoid dot(.) and use the formula. In any cell you can use these formula.
Total-1 =sum(c4:e4)
Total-2 =sum(c4:c15)
Total =sum(f4:f15)
Average =sum(c4:c15)/12
.% of Total =c16/f16*100
At first input data like the image file. After that write these formula any of the cell in the spreadsheet.
For your convenience, you can download the soft copy of this post.
Click for download soft copy (http://www.ziddu.com/download/12358737/Sheet2.xls.html)
Thank you
-
(http://i52.tinypic.com/11bkkfb.jpg)
Solution:
(http://i54.tinypic.com/2a818b7.jpg)
Find out the 10% of Sales of each employees after that add to Basic salary with it.
Formula is = b7+c7*10%
You can download the soft copy from the following link
Click here for dowonload (http://www.ziddu.com/download/12380439/Book1.xls.html)
-
How to calculate discount and interest rate?
(http://i56.tinypic.com/2ec27vc.jpg)
Soft copy Click here (http://www.ziddu.com/download/12520792/Part6.xls.html)
-
(http://i51.tinypic.com/1q6c81.png)
For soft copy Click here:
(http://www.ziddu.com/download/12527553/Part-7.xls.html)
Do you face any problem, just inform me.
-
I am regular viewer of this post. Thank you Mr. Ashiq. Keep it up.
-
Nice to know about it. Anyway, My next comment on Grade sheet, Result sheet, Best quiz count. I am working for preparing it.
thank you.
-
Dear viewer of my this post. In each part, first of all I mention the cell no with the headline after I mention formula. Every formula starts from "=" sign.
Thank you
-
(http://i54.tinypic.com/f3dvmv.jpg)
Click here :-Xfor Soft copy (http://www.ziddu.com/download/12536361/Part-8.xls.html)
To be continued...
-
(http://i53.tinypic.com/2m7awzl.jpg)
click here for soft copy (http://www.ziddu.com/download/12536537/Part-8.xls.html)
-
Good job Mr.Ashiq, keep it up.
-
(http://i51.tinypic.com/111nv3a.jpg)
Click here for soft copy (http://www.ziddu.com/download/12609985/Part-10.xls.html)
-
Thank you Faham bhai.
Dear Viewers,
If you have any query, you can ask me.
Thank you.
-
(http://i51.tinypic.com/2hhj1av.jpg)
Click here for soft copy (http://www.ziddu.com/download/12614473/Part-11.xls.html)
-
First of all type the quiz 1, quiz 2 and quiz 3 field. This sheet will automatically updated. After that type the attendance , Assignment , Presentation etc number. You will get your desire number.
Finally, Total grade point indicates the total grade point (TGP) and Total grade indicates the Total Grade.
-
(http://i52.tinypic.com/27zd6ao.jpg)
(http://i53.tinypic.com/21l7sd2.jpg)
-
G.P.A =IF(D10>=80,"4",IF(AND(D10<80,D10>=75),"3.75",IF(AND(D10<75,D10>=70),"3.50",IF(AND(D10<70,D10>=65),"3.25",IF(AND(D10<65,D10>=60),"3.00",IF(AND(D10<60,D10>=55),"2.75",IF(AND(D10<55,D10>=50),"2.50","0.00")))))))
Grade
=IF(D10>=80,"A+",IF(AND(D10<80,D10>=75),"A",IF(AND(D10<75,D10>=70),"A-",IF(AND(D10<70,D10>=65),"B+",IF(AND(D10<65,D10>=60),"B",IF(AND(D10<60,D10>=55),"B-",IF(AND(D10<55,D10>=50),"C+","F")))))))
Total Point
=SUM(E10+E11+E12+E13+E14+E15+E16+E17+E18+E19+E20+E21+E22+E23+E24+E25+E26+E27+E28+E29+E30+E31+E32+E33+E34+E35+E40+E41+E42+E43+E44+E45+E46+E47+E48+E54+E55+E56+E57+E58)/COUNT(D10:D58)
Grade
=IF(D10>=80,"A+",IF(AND(D10<80,D10>=75),"A",IF(AND(D10<75,D10>=70),"A-",IF(AND(D10<70,D10>=65),"B+",IF(AND(D10<65,D10>=60),"B",IF(AND(D10<60,D10>=55),"B-",IF(AND(D10<55,D10>=50),"C+","F")))))))
-
thanks for ur valuable posting. i am looking forward more and more.
i will be very grateful to u if u pls provide the details about power point.
-
Thank you Mr. Aarif for your wonderful comment. Yes, I will start the tutorial about MS. Power Point after completing the Ms. Excel. Inshallah. I have to make first all of the post before posting. So, it will be very difficult to maintain 2 section (Excel and Power point) for me.
Thank you Mr. Aarif.
-
Thank you Mr. Aarif for showing interest on MS Power Point.
I have to make first all of the post before posting. So, it will be very difficult to maintain 2 section (Excel and Power point) for me.
I can help you. I will start Power point tutorial and continue it. When you will finish your Excel part you can join with us. @Ashiq
-
(http://i54.tinypic.com/iydl54.jpg)
Click here for soft copy (http://www.ziddu.com/download/12633066/Excel13.xls.html)
-
Formulas:
Level:
=IF(C18>9001,$B$7,IF(AND(C18>7501,C18<=9000),$B$8,IF(AND(C18>6001,C18<=7500),$B$9,IF(AND(C18>4501,C18<=6000),$B$10,IF(AND(C18>3001,C18<=4500),$B$11,IF(AND(C18>2001,C18<=3000),$B$12,IF(C18<=2000,$B$13)))))))
Bonus:
=IF(C18>9001,C18*75%,IF(AND(C18>7501,C18<=9000),C18*70%,IF(AND(C18>6001,C18<=7500),C18*65%,IF(AND(C18>4501,C18<=6000),C18*60%,IF(AND(C18>3001,C18<=4500),C18*55%,IF(AND(C18>2001,C18<=3000),C18*50%,IF(AND(C18>1,C18<=2000),C18*45%)))))))
Total Salary:
=SUM(C18+E18)
N.B: Put all of the function D18, E18, F18 no cells according to the Headline.
-
I can help you. I will start Power point tutorial and continue it. When you will finish your Excel part you can join with us. @Ashiq
It is really good news for us. We will make this forum more effective and fulfill this forum from every side. Inshallah.
We are waiting for viewing your post. It will be helpful for everyone.
Thank You Mr. Faham bhai.
-
(http://i56.tinypic.com/24zm8sw.jpg)
For soft copy Click here (http://www.ziddu.com/download/12649650/Sheet7.xls.html)
-
Formula of Weekly Wage
WEEKLY PAY
=sum (Hour Cell Address*Rate Cell Address) =SUM(B16*C16)
OVERTIME PAY
=IF(B16>40,(B16-40)*C16*2,0)
GROSS PAY
=Sum(weekly pay cell address + Overtime Pay Cell Address) =SUM(D16+E16)
TAX
=IF(F16<=2000,F16*$B$6,IF(AND(F16>2001,F16<=3000),F16*$B$7,IF(AND(F16>3001,F16<=4500),F16*$B$8,IF(AND(F16>4501,F16<=6000),F16*$B$9,IF(AND(F16>6001,F16<=7500),F16*$B$10,IF(AND(F16>7501,F16<=9000),F16*$B$11,IF(F16>9001,F16*$B$12)))))))
TOTAL
=sum(Gross Pay Cell Address-Tax Pay Cell Address) =SUM(F16-G16)
Thank you
-
(http://img221.imageshack.us/img221/9360/77958014.jpg)
Click here for soft copy (http://img221.imageshack.us/img221/9360/77958014.jpg)
-
Topic name: ::Student Grade Sheet::
Number distribution
Class attendance =7
Quiz =10
Assignment =5
Mid term =20
Presentation =5
Lab =13
Final =40
Total =100
(N.B: "IF" function can't be used more than 8 times.)
So, I have created the number of 2 Grade column and the number of 2 GPA column.
Suppose, the course instructor has taken 4 quiz. So, I have averaged 4 quiz and continue it.
-
Functions of Student Grade Sheet
Quiz
=SUM(D17:G17)/4
Total
=SUM(C17+H17+I17+J17+K17+L17+M17)
Grade-1
=IF(N17>=80,"A+",IF(N17>=75,"A",IF(N17>=70,"A-",IF(N17>=65,"B+",IF(N17>=60,"B",IF(N17>=55,"B-",IF(N17>=50,"C+",IF(N17>=45,"C","Grade-2"))))))))
Grade-2
=IF(N17<=39,"F",IF(N17<=44,"D","N/A"))
G.P.A-1
=IF(N17>=80,"4.00",IF(N17>=75,"3.75",IF(N17>=70,"3.50",IF(N17>=65,"3.25",IF(N17>=60,"3.00",IF(N17>=55,"2.75",IF(N17>=50,"2.5",IF(N17>=45,"2.25","GPA-2"))))))))
G.P.A-2
=IF(N17<=39,"0",IF(N17<=44,"2","N/A"))
Prepared by Ashiq Hossain
-
(N.B: "IF" function can't be used more than 8 times.)
So, I have created the number of 2 Grade column and the number of 2 GPA column.
"IF" function can't support more than 8 times. It's a problem.
Showing in 2 column and linked 2 column is a great idea :)
-
;D ;D ;D ;D ;D ;D ;D ;D
"IF" function can't support more than 8 times. It's a problem.
Showing in 2 column and linked 2 column is a great idea :)
haha...vhai, khaiya daiya amar kam nai. tai notun notun idea bar kori....
Thank you Mr.FAHAM bhai
-
Faham bhai, I have generated a new idea after reading your comment. Than you will see the final grade and gpa in one column. Wait for a moment, , I will share with you after preparing.
-
(http://i56.tinypic.com/23w205f.jpg)
-
Formulas
F.Grade
=IF(P17="F","F",IF(P17="D","D",O17))
F.Gpa
=IF(S17="0","0",IF(S17="2","2",R17))
-
Thank you Faham bhai, I have learnt a new formula only for you. If you didn't comment on my post, I would not generate this formula.
-
(http://i56.tinypic.com/2wq92tg.jpg)
Click here for soft copy (http://www.ziddu.com/download/12674879/Sheet8.xls.html)
-
::Functions of Calculating Depreciation::
Depreciation ( Yearly) =SLN(Cost,Salvage Value, Life Time)
Formula
=SLN(E10,E11,E12)
Depreciation (Monthly) =SLN(Cost,Salvage Value, Life Time)/12
Forumla
=SLN(E10,E11,E12)/12
-
(http://i51.tinypic.com/30lhxcm.jpg)
-
You can maintain in your account at home by using MS excel.
Sometimes, We forget our A/C balance. That time, we cannot say exactly the balance amount. So, if you prepare like this, you can maintain your account status easily.
I have been maintaining my bank a/c information from the beginning of opening my savings account.
-
Functions:
Balance: Type in Cell no: E10, =Deposit-Withdraw =(C10-D10)
Balance: Type in cell no: E11, =sum(Last Balance E10+ Deposit C11)-Withdraw D11 =SUM(E10+C11)-D11
When you deposit money in your a/c, this deposit amount always plus with your previous balance.
Ex: you deposited yesterday 10000 tk. today you deposited 20000 tk. So, the total amount is 30,000/=tk.
So, I have used the function =SUM(E10+C11)-D11 .
As you can see in your right hand side, Deposit, Withdraw and balance. These reflect the total number of transaction ( Deposit and withdraw). Balance reflects the difference between Deposit and Withdraw.
Function of Deposit : =SUM(C10:C101)
function of Withdraw: =SUM(D10:D101)
Function of Balance: =(H12-I12)
-
(http://i56.tinypic.com/2cn73aq.jpg)
-
(http://i52.tinypic.com/25iogt0.jpg)
-
First of all, you can see January, February, March.....December indicates your rent collection amount. and Accrued/green color column reflects your unearned rent/accrued rent.
Lets see cell no C19 & D20, It reflects the total sum of collected money and uncollected money accordingly.
Suppose, Ashiq Cell no C9 monthly pay the rent to me about 3500 tk. As you can see in cell no D9 is 0 because there is no accrued is available. On the other hand, in the month of February, as you can see Ashiq pays 3200 tk. So, accrued amount is 300. It also plus with next month accrued payment. (cell no J9). If any person don't give then it will automatically show you the total accrued in every month.
Suppose, Rifat pays 1550 tk. in every month. Cell no C11, Mr. Rifat didn't pay any money in the month of January. So, the accrued amount is 1550. But he paid in the month of February, He paid 3000 tk. so the accrued amount is 100tk.
-
Cell no AE9 reflects the Total amount of rent which you have collected and AE19 shows your total income in a year.
-
Functions of Part-19 and part-20
Cell no D9 (Accrued) the function is =3500-C9
N.B. Suppose, I have got in every month about 3500 tk. so, the function start with =3500-c9 . If your get the rent 8000 then type =8000-C9 instead of =3500-c9.
Cellno F9, the function is =3500-E9+D9
Cell no H9 the function is =3500-G9+F9
Cell no J9, the function is =3500-I9+H9
I hope, you understand the consistency of the function.
Cell no C19 (Total) the function is =SUM(C9:C17)
Cell no D20 the function is =SUM(D9:D17)
AE9 no cell the function is =C9+E9+G9+I9+K9+M9+R9+T9+V9+X9+Z9+AB9
AE19 no cell the function is =C19+E19+G19+I19+K19+M19+R19+T19+V19+X19+Z19+AB19
Thank you everybody
-
(http://i56.tinypic.com/33avrb9.jpg)
(http://i53.tinypic.com/21ap7p.jpg)
-
Red Color indicates the Collection amount and Green color indicates the uncollected amount.
First of all, cell no B7, no shop monthly pays 1200 tk. So, there is no unpaid amount available in January-May. However, in month of June, I collected 1000 tk. instead of 1200. So, there was remaining 200 tk. Every work is automatically done by Ms.Excel.
-
Type on the Cell no C7 is =1200-B7
NB. Suppose your rent is 5000 taka. then type on the cell no C7 is =5000-B7
Type on the Cell no C8 is =1200+C7-B8
NB. Suppose your rent is 5000 taka. then type on the cell no C8 is =5000+C7-B8
Accordingly, C9, C10, C11 no cell =1200+C8-B9, =1200+C9-B10, =1200+C10-B11
Let's go for download soft copy (http://www.ziddu.com/download/12760089/ChouranghiMarket2010.xls.html)
-
(http://i55.tinypic.com/vh6gcw.jpg)
-
Own Capital .=Total Expense*20% ==C6*20%
Loan Amount .=Total Expense*80%==C6*80%
Payment .=pmt(Interst/12,Term*12,-Loan Amount) =PMT(A6/12,B6*12,-E6)
for soft copy (http://www.ziddu.com/download/12807771/Part-22.xls.html)
-
i will be very grateful to u if u pls provide the details about power point.
We told you that we will start PowerPoint tutorial very soon.
It will take few more days to start this topic because we are very busy for assignment and reporting writing.
bdw, we will try to start this topic as soon as possible.
Sorry for this delay @Aarif
-
Mr. Faham Bhai when will you start the power point tutorial?
-
Mr. Faham Bhai when will you start the power point tutorial?
sorry for my delay...
Finally I can able to start :)
http://forum.daffodilvarsity.edu.bd/index.php/topic,3694.0.html
-
ooohh...Finallly, I have recovered all of my pic which I uploaded by Imageshack....Thank you my Creator.
-
(http://i54.tinypic.com/2hqgw0p.jpg)
(http://i56.tinypic.com/dno9aq.jpg)
-
(http://i55.tinypic.com/ogf4w6.jpg)
-
Click here for soft Copy (http://www.ziddu.com/download/15687530/RiskFreeRate.xls.html)
-
Good. Go ahead.
-
Thank you.