With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Friday, April 24, 2015
yogi_Compute Bonus For Members Of Teams On Specified Week By Week Basis
Google Spreadsheet Post #1949 Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MIwww.energyefficientbuild.com. Apr-24, 2015 question by Jeff Underhill:
Formula Needed: I'm attempting to count variables p, l, and u (p = prepared; l = late; u = unprepared) in sheet Q4 by weeks on sheet Weekly Bonus XP. I need the formula to be on sheet Weekly Bonus XP in each cell, beginning with cell H2 through V12. I need the new formula to use this formula from sheet Q4
in an array formula, summing the above variables based on dates. The formula would use the date from sheet Q4 in column A and match it to the range between Monday and Friday on sheet Weekly Bonus XP in rows C through G.
For example, for each p, l, or u David earns in column C between March 31 and April 3 on sheet Q4, the formula on sheet Weekly Bonus XP would sum those values in the week March 31 - April 3 in cell H2.
I know precisely what I'm trying to accomplish and have tried many variations, to no avail. I am studying the syntax, and your help would be very appreciated!!
I'm attempting to count the p, l, and u variables in sheet Q4 based on dates and sum them in a cell, per week and student, on sheet Weekly Bonus XP. For each student name in Q4, I need to count up the p, l, and u variables in that student's column IF those variables fall in a date range.
I need a formula on sheet Weekly Bonus XP in cell H2.
I need that formula to look something like =arrayformula(COUNTIF(C4:C,"p")*5+COUNTIF(C4:C,"e")*5+COUNTIF(C4:C,"l")*3+COUNTIF(C4:C,"u")*1)sum(date...))
p = 5 points
e = 5 points
l = 3 points
u = 1 point
So sheet Q4 column C variables will be summed for the week for the student on Sheet Weekly Bonus XP cell H2. That is, IF the date in sheet Q4 column C matches the range of dates in sheet Weekly Bonus XP between columns C2 and G2. The only dates needed in the formula would be C2 and G2, I imagine.
I would like the formula to be reiterative for each student and week.