# 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. ### DrakendodertjeGuest

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

=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

2. ### Claus BuschGuest

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

3. ### DrakendodertjeGuest

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