Hi, again.
Formula for working hour in EXCEL
Example :
A1 : 08:15
B1 : 18:45
C1 : how many working hour ?
This is the formula : =((B1-A1+(B1
The result will be 10.5 Working Hour.
How to using SUMIF and SUMIFS
SUMIF(range;criteria;[sum_range]) is a formula to summarize with 1 criteria or categories
example :
a;b;c;d
1;a ; 10 ; a1
2;b ; 20 ; c1
3;c ; 30 ; a1
4;d ; 40 ; b1
5;e ; 50 ; b1
6;f ; 60 ; a1
7;g ; 70 ; c1
total C is 280
sum for a1 is =sumif(d1:d7;"a1";c1:c7) = 120
sum for b1 is =sumif(d1:d7;"b1";c1:c7) = 140
sum for c1 is =sumif(d1:d7;"c1";c1:c7) = 20
SUMIFS(sum_range, criteria_range, criteria 1, criteria_range, criteria2;) is a formula to summarize with more than 1 criteria or categories
example :
a;b;c;d
1;a ; 10 ; 34
2;b ; 20 ; 16
3;c ; 30 ; 24
4;d ; 40 ; 5
5;e ; 50 ; 9
6;f ; 60 ; 21
7;g ; 70 ; 27
total C is 280
sum for d between 1 and 20 =SUMIFS(b1:b7;d1:d7;">=1";d1:d7;"<21") = 110
sum for d between 21 and 35 =SUMIFS(b1:b7;d1:d7;">=20";d1:d7;"<35") = 170
Combining Cells in Excel
combine multiple cell ( minimum with 2 cells ) with &
Example :
cell a1 : Together
cell b1 : Forever
with this formula we can get TogetherForever.
How do you do it ? Heres how, just use this formula =a1 & b1
And You can use space to get Together Forever with this formula = a1 & " " & b1
Interesting isn't it?
Combine text with number too.
example :
cell a1 : 4
cell b1 : Ever
How : ="Together " & a1 & b1 ==> you'll get Together 4ever
combine text with date
="today is : " & today() ; u'll get today is : 40749
to fix this :
="today is : " & text(today();"dddd") ; u'll get today is : monday
For Others :
Format a date
="Payment is due " & TEXT(today(),"dd mmmm yyyy")result : 25 July 2011Format number as currency
="Amount due: " & TEXT(2500000;"Rp #.##,##00")& " Idr"result : Amount due Rp 2.500.000,00 Idr
Format a number as a percentage:
="Your Popularity is " & TEXT(0.8,"0%")
result : Your Popularity is 80%
Format a number as a fraction:
="Hours worked: " &TEXT(5.25,"# ?/?")
result : Hours worked: 5 1/4
How to Insert Excel in Word
But i'm gonna show u'all how to insert a long excel to one page word.
First open your excel and use page break preview to see your work in 1 page.
Second copy you excel and open your word and go to Edit , Paste Special and Picture (Enhanced Metafile).
Third well your excel become an image and you can move or drag or even resized.
Well believe it or not....
Remove multiple hyperlink in Excel
- Type the number 1 in a blank cell, and right-click the cell.
- Click Copy on the shortcut menu.
- While pressing CTRL, select each hyperlink
(hyperlink: Colored and underlined text or a graphic that you click to
go to a file, a location in a file, a Web page on the World Wide Web,
or a Web page on an intranet. Hyperlinks can also go to newsgroups and
to Gopher, Telnet, and FTP sites.) you want to deactivate.
To select a cell that has a hyperlink (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.) in it without jumping to the hyperlink destination, click the cell and hold the mouse button until the cursor becomes a cross, then release the mouse button.
- Click Paste Special on the Edit menu.
- Under Operation, click Multiply and then click OK.
Symbol/Codes for Life
Press Alt + [4 digits Codes]
Common Symbols And Codes
Euro € 0128
Ellipsis … 0133
Dagger † 0134
Double Dagger ‡ 0135
Bullet • 0149
Trademark ™ 0153
Cents ¢ 0162
Pounds £ 0163
Yen ¥ 0165
Copyright © 0169
Registered ® 0174
Plus/Minus ± 0177
Paragraph ¶ 0182
Degree ° 0176
Tips : too check all the symbols or codes in excel : =CHAR(ROW())
The "inverse" of the CHAR function is the CODE function. While CHAR takes a number and returns the actual character, the CODE function takes a character and returns the code number
No comments:
Post a Comment