Remember Me?

#1
September 24th 13, 05:39 PM
 Junior Member First recorded activity by ExcelBanter: Sep 2013 Posts: 2
The sum of the same function that is used for a range of cells

Hi all,

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

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

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

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

=SUM(IF(0,5-C40;0,5-C4;0);IF(0,5-C50;0,5-C5;0);IF(0,5-C60;0,5-C6;0);IF(0,5-C70;0,5-C7;0);IF(0,5-C80;0,5-C8;0);IF(0,5-C90;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. I tried this:

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

It seemed logical to me, but apparently this doesn't work. Does somebody know the proper notation for a range, because this would mean it is much easier for the user of the sheet to insert a new employee in the future 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 D9 in this case. I hope/think the same answer for notation applies here as well, but i just add it for the sake of complete info.

Thanks so much to anyone who replies in advance,

sincerely,

Joris

#2
September 24th 13, 09:16 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,651
The sum of the same function that is used for a range of cells

Hi Joris,

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

=SUM(IF(0,5-C40;0,5-C4;0);IF(0,5-C50;0,5-C5;0);IF(0,5-C60;0,5-C6;0);IF(0,5-C70;0,5-C7;0);IF(0,5-C80;0,5-C8;0);IF(0,5-C90;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
#3
September 25th 13, 10:46 AM
 Junior Member First recorded activity by ExcelBanter: Sep 2013 Posts: 2

Quote:
 Originally Posted by Claus Busch Hi Joris, Am Tue, 24 Sep 2013 17:39:02 +0100 schrieb Drakendodertje: =SUM(IF(0,5-C40;0,5-C4;0);IF(0,5-C50;0,5-C5;0);IF(0,5-C60;0,5-C6;0);IF(0,5-C70;0,5-C7;0);IF(0,5-C80;0,5-C8;0);IF(0,5-C90;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
Hi Claus,

Thanks for your assistance, it's much appreciated

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Mark Excel Discussion (Misc queries) 3 May 29th 09 10:02 PM jwbuyer Excel Worksheet Functions 2 February 24th 09 06:41 PM firsttimer Excel Worksheet Functions 2 May 29th 07 09:43 AM Christopher Brooks[_2_] Excel Programming 2 June 14th 04 01:51 PM Paul Excel Programming 0 July 9th 03 10:42 PM

All times are GMT +1. The time now is 07:50 AM.