The sum of the same function that is used for a range of cells

Discussion in 'Excel Beginners' started by Drakendodertje, Sep 24, 2013.

  1. Hi all,

    With no background in excel i'm trying to create a sheet in wich we ca
    schedule the worktime from various employees for a charit
    organisation.

    One of the functions i'm trying to implement is to show if respectivel
    the morning, afternoon and evening are under- or overstaffed, or jus
    adequately. For example i designed a function for the morninghours to b
    read from a cell C4:

    =IF(0,5-C4>0;0,5-C4;0)

    now i need the sum of using this function seperately for differen
    cells. So i designed it like this:

    =SUM(IF(0,5-C4>0;0,5-C4;0);IF(0,5-C5>0;0,5-C5;0);IF(0,5-C6>0;0,5-C6;0);IF(0,5-C7>0;0,5-C7;0);IF(0,5-C8>0;0,5-C8;0);IF(0,5-C9>0;0,5-C9;0))

    This works. But like the SUM(C4:C9) formula, i would prefer to brin
    this formula down to a range instead of inserting the function for eac
    cell seperately. I tried this:

    =SUM(IF(0,5-(C4:C9)>0;0,5-(C4:C9);0)) an
    =IF(0,5-(C4:C9)>0;0,5-(C4:C9);0)

    It seemed logical to me, but apparently this doesn't work. Does somebod
    know the proper notation for a range, because this would mean it is muc
    easier for the user of the sheet to insert a new employee in the futur
    without having to alter the affected formulas himself.

    I also have a new function that reads like this:

    =IF((IF(D4-0,5)>(5/24); (5/24); D4-0,5))>0; (IF(D4-0,5>(5/24); (5/24)
    D4-0,5)); 0)

    This works also, but again i would like to use it for the range D4 to D
    in this case. I hope/think the same answer for notation applies here a
    well, but i just add it for the sake of complete info.

    Thanks so much to anyone who replies in advance,

    sincerely,

    Jori


    --
    Drakendodertje
     
    Drakendodertje, Sep 24, 2013
    #1
    1. Advertisements

  2. Drakendodertje

    Claus Busch Guest

    Hi Joris,

    Am Tue, 24 Sep 2013 17:39:02 +0100 schrieb Drakendodertje:

    > =SUM(IF(0,5-C4>0;0,5-C4;0);IF(0,5-C5>0;0,5-C5;0);IF(0,5-C6>0;0,5-C6;0);IF(0,5-C7>0;0,5-C7;0);IF(0,5-C8>0;0,5-C8;0);IF(0,5-C9>0;0,5-C9;0))
    >
    > This works. But like the SUM(C4:C9) formula, i would prefer to bring
    > this formula down to a range instead of inserting the function for each
    > cell seperately.


    try:
    =SUM(IF(C4:C9<0.5,0.5-C4:C9))
    and enter the arrayy formula with CTRL+Shift+Enter


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Sep 24, 2013
    #2
    1. Advertisements

  3. Claus Busch;1614028 Wrote:
    > Hi Joris,
    >
    > Am Tue, 24 Sep 2013 17:39:02 +0100 schrieb Drakendodertje:
    > -
    >
    > =SUM(IF(0,5-C4>0;0,5-C4;0);IF(0,5-C5>0;0,5-C5;0);IF(0,5-C6>0;0,5-C6;0);IF(0,5-C7>0;0,5-C7;0);IF(0,5-C8>0;0,5-C8;0);IF(0,5-C9>0;0,5-C9;0))
    > >
    > > This works. But like the SUM(C4:C9) formula, i would prefer to bring
    > > this formula down to a range instead of inserting the function fo

    > each
    > > cell seperately. -

    >
    > try:
    > =SUM(IF(C4:C9<0.5,0.5-C4:C9))
    > and enter the arrayy formula with CTRL+Shift+Enter
    >
    >
    > Regards
    > Claus B.
    > --
    > Win XP PRof SP2 / Vista Ultimate SP2
    > Office 2003 SP2 /2007 Ultimate SP2


    Hi Claus,

    Thanks for your assistance, it's much appreciated

    Your suggestion worked liked a charm. I guess this is my introduction t
    CSE-formulas :


    --
    Drakendodertje
     
    Drakendodertje, Sep 25, 2013
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. frederic
    Replies:
    5
    Views:
    151
    David McRitchie
    Oct 9, 2005
  2. Not excelling at macros

    pasting non-contiguous range of cells to new row, same cell locati

    Not excelling at macros, Apr 3, 2006, in forum: Excel Beginners
    Replies:
    3
    Views:
    81
    Ron de Bruin
    Apr 4, 2006
  3. UpTheCreek
    Replies:
    1
    Views:
    222
    gls858
    Oct 31, 2006
  4. Tan
    Replies:
    2
    Views:
    228
    Don Guillett
    Mar 14, 2007
  5. Raghu
    Replies:
    2
    Views:
    98
    ShaneDevenshire
    Sep 25, 2008
  6. Eisaz
    Replies:
    15
    Views:
    178
    T. Valko
    Nov 19, 2008
  7. T. Otten
    Replies:
    4
    Views:
    174
    Don Guillett
    Jan 17, 2009
  8. Vinay Vasu

    SUM of a colum where IF function is used

    Vinay Vasu, Apr 17, 2010, in forum: Excel Beginners
    Replies:
    1
    Views:
    98
    Joe User
    Apr 17, 2010
Loading...