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
|