# shortcuts.docx | Microsoft Excel | Database Index

View again

All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

## Documents

Published:

Views: 24 | Pages: 10

Share
Related documents
Description
Course Categories Excel Formulas: 10 Formulas That Helped Me Keep My Job APRIL 19, 2013 BY STEVE QUATRANI Don’t waste any more hours in Microsoft Excel doing things manually. There are many ways to use Excel formulas to decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports. Excel Formulas You Should Definitely Know: 1. SUM Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5) The SUM formula does exactly what you would expect. It allo
Transcript
Course Categories Excel Formulas: 10 Formulas That Helped Me Keep My Job  APRIL 19, 2013 BY  STEVE QUATRANI Don’t waste any o!e o#!s \$n %\$&!oso't E(&e) *o\$n+ t \$n+s an#a))y T e!e a!e any ways to #se E(&e) 'o!#)as to *e&!ease t e ao#nt o' t\$e yo# s-en* \$n E(&e) an* \$n&!ease t e a&&#!a&y o' yo#! *ata an* yo#! !e-o!ts Excel Formulas You Should Definitely Know: 1. SU .o!#)a/ SU%,  o! SU%A1, B1 o! SU%A1/BT e SU% 'o!#)a *oes e(a&t)y w at yo# wo#)* e(-e&t It a))ows yo# to a** 2 o! o!e n#4e!s to+et e! Yo# &an #se &e)) !e'e!en&es as we)) \$n t \$s 'o!#)aT e a4o5e s ows yo# *\$''e!ent e(a-)es Yo# &an a5e n#4e!s \$n t e!e se-a!ate* 4y &oas an* \$t w\$)) a** t e to+et e! 'o! yo#, yo# &an a5e &e)) !e'e!en&es an* as )on+ as t e!e a!e n#4e!s \$n t ose &e))s \$t w\$)) a** t e to+et e! 'o! yo#, o! yo# &an a5e a !an+eo' &e))s w\$t a &o)on \$n 4etween t e 2 &e))s, an* \$t w\$)) a** t e n#4e!s \$n a)) t e &e))s \$n t e !an+e !. C U#\$ .o!#)a/ 67UNTA1/A10T e &o#nt 'o!#)a &o#nts t e n#4e! o' &e))s \$n a !an+e t at a5e n#4e!s \$n t eT \$s 'o!#)a on)y wo!8s w\$t n#4e!s t o#+ /  It on)y &o#nts t e &e))s w e!e t e!e a!e n#4e!s **Learn more about the COUNT function in this on-demand, online course. FREE preie!** %. C U#\$& .o!#)a/ 67UNTAA1/A106o#nts t e n#4e! o' none-ty &e))s \$n a !an+e It w\$)) &o#nt &e))s t at a5e n#4e!s an*:o! any ot e! & a!a&te!s \$n t eT e 67UNTA .o!#)a wo!8s w\$t a)) *ata ty-esIt &o#nts t e n#4e! o' none-ty &e))s no atte! t e *ata ty-e '. (E# .o!#)a/ LENA1T e LEN 'o!#)a &o#nts t e n#4e! o' & a!a&te!s \$n a &e)) Be &a!e'#) t o#+ ; T \$s \$n&)#*es s-a&es  Not\$&e t e *\$''e!en&e \$n t e 'o!#)a !es#)ts/ 10 & a!a&te!s w\$t o#t s-a&es \$n 4etween t e wo!*s, 12 w\$t s-a&es 4etween t e wo!*s ). \$*+ .o!#)a/ TRI%A1<ets !\$* o' any s-a&e \$n a &e)), e(&e-t 'o! s\$n+)e s-a&es 4etween wo!*s I’5e 'o#n* t \$s 'o!#)a to 4e e(t!ee)y #se'#) 4e&a#se I’5e o'ten !#n \$nto s\$t#at\$ons w e!e yo# -#)) *ata '!o a *ata4ase an* 'o! soe !eason e(t!a s-a&es a!e -#t \$n 4e \$n* o! \$n '!ont o' )e+\$t\$ate*ata T \$s &an w!ea8 a5o& \$' yo# a!e t!y\$n+ to &o-a!e #s\$n+ I. stateents o! VL77=UP’sI a**e* \$n an e(t!a s-a&e 4e \$n* >I Lo5e E(&e)? T e TRI% 'o!#)a !eo5es t at e(t!a s-a&e 6 e&8 o#t t e & a!a&te! &o#nt *\$''e!en&e w\$t an* w\$t o#t t e TRI% 'o!#)a ,. *+-\$/ (EF\$/ +D .o!#)as/  RI<@Tte(t, n#4e! o' & a!a&te!s, LE.Tte(t, n#4e! o' & a!a&te!s, %IDte(t, sta!t n#4e!, n#4e! o' & a!a&te!sNote/ In a)) o' t ese 'o!#)as, w e!e5e! \$t says >te(t? yo# &an #se a &e)) !e'e!en&e as we))T ese 'o!#)as !et#!n t e s-e&\$'\$e* n#4e! o' & a!a&te!s '!o a te(t st!\$n+ RI<@T +\$5es yo# t e n#4e! o' & a!a&te!s '!o t e !\$+ t o' t e te(t st!\$n+, LE.T +\$5es yo# t e n#4e! o' & a!a&te!s '!o t e )e't, an* %ID +\$5es yo# t e s-e&\$'\$e* n#4e! o' & a!a&te!s '!o t e \$**)e o' t e wo!* Yo# te)) t e %ID 'o!#)a w e!e to sta!t w\$t t e sta!tn#4e! an* t en \$t +!a4s t e s-e&\$'\$e* n#4e! o' & a!a&te!s to t e !\$+ t o' t e sta!tn#4e!I #se* t e LE.T 'o!#)a to +et t e '\$!st wo!* I a* \$t )oo8 \$n &e)) A1 an* +!a4 on)y t e 1st & a!a&te! '!o t e )e't T \$s +a5e #s t e wo!* >I? '!o >I )o5e E(&e)?I #se* t e %ID 'o!#)a to +et t e \$**)e wo!* I a* \$t )oo8 \$n &e)) A1, sta!t at & a!a&te! 3, an* +!a4  & a!a&te!s a'te! t at T \$s +\$5es #s #st t e wo!* >)o5e? '!o >I )o5e E(&e)?I #se* t e RI<@T 'o!#)a to +et t e )ast wo!* I a* \$t )oo8 at &e)) A1 an* +!a4 t e '\$!st C & a!a&te!s '!o t e !\$+ t T \$s +\$5es #s >E(&e)? '!o >I )o5e E(&e)? 0. ( KU2 .o!#)a/ VL77=UP)oo8#-5a)#e, ta4)ea!!ay, &o)\$n*e(n#, !an+e)oo8#-  By 'a! y ost #se* 'o!#)a T e o''\$&\$a) *es&!\$-t\$on o' w at \$t *oes/ >Loo8s 'o! a 5a)#e \$n t e )e'tost &o)#n o' a ta4)e, an* t en !et#!ns a 5a)#e \$n t e sae !ow '!o a &o)#n yo# s-e&\$'y?  ee the full e#planation of \$LOO%U&  Bas\$&a))y, yo# *e'\$ne a  5a)#e t e )oo8#-5a)#e 'o! t e 'o!#)a to )oo8 'o! It )oo8s 'o! t \$s 5a)#e \$n t e )e'tost &o)#n o' a ta4)e t e ta4)ea!!ayNote/ I' at a)) -oss\$4)e #se a n#4e! 'o! t e )oo8#-5a)#e T \$s a8es \$t a )ot eas\$e! to a8e s#!e t e *ata yo# a!e +ett\$n+ 4a&8 \$s a &o!!e&t at& I' \$t '\$n*s a at& o' t e >)oo8#-5a)#e? \$n t e )e't &o)#n o' t e >ta4)ea!!ay? \$t w\$)) !et#!n t e 5a)#e \$n t e &o)#n yo# s-e&\$'y #s\$n+ t e >\$n*e(n#? T e >\$n*e(n#? \$s !e)at\$5e to t e )e't ost &o)#n So, \$' yo# a5e t e ta4)e\$n*e( )oo8 \$n &o)#n A an* yo#  want w at \$s !et#!ne* to 4e w at’s \$n &o)#n B t e >\$n*e(n#? wo#)* 4e 2 4e&a#se t e )e'tost &o)#n, &o)#n A \$n t \$s &ase, \$s t e 1st &o)#n \$n t e ta4)e a!!ay an* &o)#n B \$s t e 2n* &o)#n  en&e t e 2 'o! t e \$n*e( n#4e! I' yo# want w at \$s \$n &o)#n 6 to 4e !et#!ne* yo#’* -#t 3 'o! t e \$n*e(n# T e >!an+e)oo8#-? \$s a TRUE o! .ALSE 5a)#e I' yo# -#t TRUE \$t w\$)) +\$5e yo# t e &)osest at&  I' yo# -#t .ALSE \$t w\$)) on)y +\$5e yo# an e(a&t at&  I on)y #se .ALSE w en #s\$n+ t e VL77=UP 'o!#)aE(a-)e/ Yo# a5e 2 )\$sts/ 1 w\$t a sa)es -e!son’s ID an* t e sa)es !e5en#e 'o! t e #a!te! Anot e! w\$t t e sa)es -e!son’s ID an* t e sa)es -e!son’s nae Yo# want to at& #- t e sa)es -e!son’s nae to t e sa)es -e!son’s !e5en#e n#4e!s 'o! t e #a!te! T ey a!e a))  #4)e* a!o#n* so to an#a))y at& t \$s, e5en 'o! a sa)) n#4e! o' sa)esen wo#)* )ea5e !oo 'o! a \$+ a!+\$n o' e!!o! an* ta8e a )ot o' t\$eT e '\$!st )\$st +oes '!o A1 to B13 T e 2n* )\$st +oes '!o D1 to E2In &e)) 61 I wo#)* -#t t e 'o!#)a VL77=UPB1F, GAG1/GBG13, 2, .ALSEB1F  t e )oo8#-5a)#e t e sa)es -e!son’s ID T \$s \$s a n#4e! t at a--ea!s on 4ot )\$stsGAG1/GBG13  t e >ta4)ea!!ay? T \$s \$s t e a!ea I want t e 'o!#)a to sea!& t e )e'tost &o)#n &o)#n E \$n t \$s &ase 'o! t e >)oo8#-5a)#e? I went to . 4e&a#se \$' \$t '\$n*s at& \$n &o)#n E, I want \$t to !et#!n w at’s \$n &o)#n . T e oney s\$+ns a!e t e!e so t at t e ta4)ea!!ay w\$)) stay t e sae no atte! w e!e t e 'o!#)a \$s o5e* o! &o-\$e* to T \$s \$s &a))e* an a4so)#te !e'e!en&e2  t e \$n*e(n# T \$s te))s t e 'o!#)a t e n#4e! o' &o)#ns away '!o t e )e't ost &o)#n to !et#!n \$n &ase o' at&  So, \$' yo# '\$n* a at& 4etween t e )oo8#-5a)#e an* t e )e'tost &o)#n o' t e ta4)e a!!ay, !et#!n w at’s \$n t e sae !ow \$n t e 2n* &o)#n o' t e ta4)e t e 1st &o)#n \$s a)ways t e )e'tost &o)#n It sta!ts at 1, not 0.ALSE te))s t e 'o!#)a I want \$t to on)y !et#!n t e 5a)#e \$' \$t’s an e(a&t at& I wo#)* t en &o-y an* -aste t at 'o!#)a a)on+ a)) t e &e))s \$n &o)#n 6 ne(t to t e '\$!st )\$st T \$s wo#)* +\$5e e a -e!'e&t)y a)\$+ne* )\$st w\$t t e sa)es -e!son’s ID, sa)es -e!son’s !e5en#e 'o! t e #a!te!, an* t e sa)es -e!son’s nae
Recommended

73 pages

73 pages

73 pages

3 pages

3 pages

36 pages

10 pages

7 pages

4 pages

4 pages

3 pages

11 pages

31 pages

5 pages

9 pages

View more...