{"id":34,"date":"2023-11-28T17:07:25","date_gmt":"2023-11-28T22:07:25","guid":{"rendered":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/chapter\/formulas\/"},"modified":"2024-04-01T22:16:46","modified_gmt":"2024-04-02T02:16:46","slug":"formulas","status":"publish","type":"chapter","link":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/chapter\/formulas\/","title":{"raw":"Excel Formulas","rendered":"Excel Formulas"},"content":{"raw":"
\r\n

Excel<\/strong> Commands<\/strong><\/h2>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

<\/p>\r\n<\/td>\r\n

\r\n

A<\/p>\r\n<\/td>\r\n

\r\n

B<\/p>\r\n<\/td>\r\n

\r\n

C<\/p>\r\n<\/td>\r\n

\r\n

D<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

E<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

1<\/p>\r\n<\/td>\r\n

\r\n

X<\/strong><\/p>\r\n<\/td>\r\n

\r\n

W<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Name<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Command<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Answer<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Answer (with Notes)<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

2<\/p>\r\n<\/td>\r\n

\r\n

$55,000<\/p>\r\n<\/td>\r\n

\r\n

9<\/p>\r\n<\/td>\r\n

\r\n

Mean<\/p>\r\n<\/td>\r\n

\r\n

=AVERAGE(A2:A7)<\/p>\r\n<\/td>\r\n

\r\n

$63,500<\/p>\r\n<\/td>\r\n

\r\n

Finds the mean<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

3<\/p>\r\n<\/td>\r\n

\r\n

$67,000<\/p>\r\n<\/td>\r\n

\r\n

5<\/p>\r\n<\/td>\r\n

\r\n

Median<\/p>\r\n<\/td>\r\n

\r\n

=MEDIAN(A2:A7)<\/p>\r\n<\/td>\r\n

\r\n

$64,000<\/p>\r\n<\/td>\r\n

\r\n

Finds the median<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

4<\/p>\r\n<\/td>\r\n

\r\n

$71,000<\/p>\r\n<\/td>\r\n

\r\n

12<\/p>\r\n<\/td>\r\n

\r\n

Mode<\/p>\r\n<\/td>\r\n

\r\n

=MODE(B2:B7)<\/p>\r\n<\/td>\r\n

\r\n

5<\/p>\r\n<\/td>\r\n

\r\n

Returns only one mode<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

5<\/p>\r\n<\/td>\r\n

\r\n

$62,000<\/p>\r\n<\/td>\r\n

\r\n

7<\/p>\r\n<\/td>\r\n

\r\n

Mode<\/p>\r\n<\/td>\r\n

\r\n

=MODE.MULT(B2:B7)<\/p>\r\n<\/td>\r\n

\r\n

5, 12<\/p>\r\n<\/td>\r\n

\r\n

Returns multiple modes<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

6<\/p>\r\n<\/td>\r\n

\r\n

$60,000<\/p>\r\n<\/td>\r\n

\r\n

12<\/p>\r\n<\/td>\r\n

\r\n

Sum<\/p>\r\n<\/td>\r\n

\r\n

=SUM(B2:B7)<\/p>\r\n<\/td>\r\n

\r\n

50<\/p>\r\n<\/td>\r\n

\r\n

Adds (sums) values<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

7<\/p>\r\n<\/td>\r\n

\r\n

$66,000<\/p>\r\n<\/td>\r\n

\r\n

5<\/p>\r\n<\/td>\r\n

\r\n

Sum Product<\/p>\r\n<\/td>\r\n

\r\n

=SUMPRODUCT(A2:A7, B2:B7)<\/p>\r\n<\/td>\r\n

\r\n

3,166,000<\/p>\r\n<\/td>\r\n

\r\n

Multiplies pairs, then adds them<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

8<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

Count<\/p>\r\n<\/td>\r\n

\r\n

=COUNT(A2:A7)<\/p>\r\n<\/td>\r\n

\r\n

6<\/p>\r\n<\/td>\r\n

\r\n

Count the number of values (non empty cells).<\/p>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/div>\r\n

Computing<\/strong> Weighted<\/strong> Average<\/strong><\/h2>\r\n
    \r\n \t
  • Excel formula=SUMPRODUCT(A2:A7, B2:B7)\/SUM(B2:B7) or =D7\/D6<\/span><\/li>\r\n \t
  • Answer <\/span>\u2192<\/em> 63<\/span>,<\/em> 320<\/span><\/li>\r\n<\/ul>","rendered":"
    \n

    Excel<\/strong> Commands<\/strong><\/h2>\n\n\n\n\n\n\n\n\n\n\n\n
    \n

    \n<\/td>\n

    		\n

    A<\/p>\n<\/td>\n

    		\n

    B<\/p>\n<\/td>\n

    		\n

    C<\/p>\n<\/td>\n

    		\n

    D<\/p>\n<\/td>\n

    		\n

    \n<\/td>\n

    		\n

    E<\/p>\n<\/td>\n<\/tr>\n

    \n

    1<\/p>\n<\/td>\n

    		\n

    X<\/strong><\/p>\n<\/td>\n

    		\n

    W<\/strong><\/p>\n<\/td>\n

    		\n

    Name<\/strong><\/p>\n<\/td>\n

    		\n

    Command<\/strong><\/p>\n<\/td>\n

    		\n

    Answer<\/strong><\/p>\n<\/td>\n

    		\n

    Answer (with Notes)<\/strong><\/p>\n<\/td>\n<\/tr>\n

    \n

    2<\/p>\n<\/td>\n

    		\n

    $55,000<\/p>\n<\/td>\n

    		\n

    9<\/p>\n<\/td>\n

    		\n

    Mean<\/p>\n<\/td>\n

    		\n

    =AVERAGE(A2:A7)<\/p>\n<\/td>\n

    		\n

    $63,500<\/p>\n<\/td>\n

    		\n

    Finds the mean<\/p>\n<\/td>\n<\/tr>\n

    \n

    3<\/p>\n<\/td>\n

    		\n

    $67,000<\/p>\n<\/td>\n

    		\n

    5<\/p>\n<\/td>\n

    		\n

    Median<\/p>\n<\/td>\n

    		\n

    =MEDIAN(A2:A7)<\/p>\n<\/td>\n

    		\n

    $64,000<\/p>\n<\/td>\n

    		\n

    Finds the median<\/p>\n<\/td>\n<\/tr>\n

    \n

    4<\/p>\n<\/td>\n

    		\n

    $71,000<\/p>\n<\/td>\n

    		\n

    12<\/p>\n<\/td>\n

    		\n

    Mode<\/p>\n<\/td>\n

    		\n

    =MODE(B2:B7)<\/p>\n<\/td>\n

    		\n

    5<\/p>\n<\/td>\n

    		\n

    Returns only one mode<\/p>\n<\/td>\n<\/tr>\n

    \n

    5<\/p>\n<\/td>\n

    		\n

    $62,000<\/p>\n<\/td>\n

    		\n

    7<\/p>\n<\/td>\n

    		\n

    Mode<\/p>\n<\/td>\n

    		\n

    =MODE.MULT(B2:B7)<\/p>\n<\/td>\n

    		\n

    5, 12<\/p>\n<\/td>\n

    		\n

    Returns multiple modes<\/p>\n<\/td>\n<\/tr>\n

    \n

    6<\/p>\n<\/td>\n

    		\n

    $60,000<\/p>\n<\/td>\n

    		\n

    12<\/p>\n<\/td>\n

    		\n

    Sum<\/p>\n<\/td>\n

    		\n

    =SUM(B2:B7)<\/p>\n<\/td>\n

    		\n

    50<\/p>\n<\/td>\n

    		\n

    Adds (sums) values<\/p>\n<\/td>\n<\/tr>\n

    \n

    7<\/p>\n<\/td>\n

    		\n

    $66,000<\/p>\n<\/td>\n

    		\n

    5<\/p>\n<\/td>\n

    		\n

    Sum Product<\/p>\n<\/td>\n

    		\n

    =SUMPRODUCT(A2:A7, B2:B7)<\/p>\n<\/td>\n

    		\n

    3,166,000<\/p>\n<\/td>\n

    		\n

    Multiplies pairs, then adds them<\/p>\n<\/td>\n<\/tr>\n

    \n

    8<\/p>\n<\/td>\n

    		\n

    \n<\/td>\n

    		\n

    \n<\/td>\n

    		\n

    Count<\/p>\n<\/td>\n

    		\n

    =COUNT(A2:A7)<\/p>\n<\/td>\n

    		\n

    6<\/p>\n<\/td>\n

    		\n

    Count the number of values (non empty cells).<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    Computing<\/strong> Weighted<\/strong> Average<\/strong><\/h2>\n
      \n
    • Excel formula=SUMPRODUCT(A2:A7, B2:B7)\/SUM(B2:B7) or =D7\/D6<\/span><\/li>\n
    • Answer <\/span>\u2192<\/em> 63<\/span>,<\/em> 320<\/span><\/li>\n<\/ul>\n","protected":false},"author":865,"menu_order":4,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-34","chapter","type-chapter","status-publish","hentry"],"part":3,"_links":{"self":[{"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/chapters\/34","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/wp\/v2\/users\/865"}],"version-history":[{"count":2,"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/chapters\/34\/revisions"}],"predecessor-version":[{"id":453,"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/chapters\/34\/revisions\/453"}],"part":[{"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/chapters\/34\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/wp\/v2\/media?parent=34"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/pressbooks\/v2\/chapter-type?post=34"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/wp\/v2\/contributor?post=34"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/1130sandbox\/wp-json\/wp\/v2\/license?post=34"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}