Home 
Search 
Today's Posts 
#1




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,5C40;0,5C4;0) now i need the sum of using this function seperately for different cells. So i designed it like this: =SUM(IF(0,5C40;0,5C4;0);IF(0,5C50;0,5C5;0);IF(0,5C60;0,5C6;0);IF(0,5C70;0,5C7;0);IF(0,5C80;0,5C8;0);IF(0,5C90;0,5C9;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(D40,5)(5/24); (5/24); D40,5))0; (IF(D40,5(5/24); (5/24); D40,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




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




Quote:
Thanks for your assistance, it's much appreciated Your suggestion worked liked a charm. I guess this is my introduction to CSEformulas 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Using value in two cells as the range for a MAX function  Excel Discussion (Misc queries)  
if function or lookup??? across a range of cells in a row.  Excel Worksheet Functions  
Insert range of cells with a function  Excel Worksheet Functions  
function of a range of cells  Excel Programming  
How to apply a function across a range of cells  Excel Programming 