Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 24th 13, 05:39 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2013
Posts: 2
Default 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   Report Post  
Old September 24th 13, 09:16 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default 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   Report Post  
Old September 25th 13, 10:46 AM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2013
Posts: 2
Thumbs up

Quote:
Originally Posted by Claus Busch View Post
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using value in two cells as the range for a MAX function Mark Excel Discussion (Misc queries) 3 May 29th 09 10:02 PM
if function or lookup??? across a range of cells in a row. jwbuyer Excel Worksheet Functions 2 February 24th 09 05:41 PM
Insert range of cells with a function firsttimer Excel Worksheet Functions 2 May 29th 07 09:43 AM
function of a range of cells Christopher Brooks[_2_] Excel Programming 2 June 14th 04 01:51 PM
How to apply a function across a range of cells Paul Excel Programming 0 July 9th 03 10:42 PM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017