0

Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0.

I am trying to make an excel timesheet where Employees who work on Sunday get paid at double their hourly rate. HOWEVER, in order for them to get this double rate they MUST have worked all of the previous 6 days. (i.e Monday to Saturday) Otherwise they just get the usual hourly rate.

Got myself rather confused!! Any pointers please?!!!

Ramhound
  • 42,708
user193896
  • 15

2 Answers2

2

The following formula will do the trick:

=IF(COUNTIF(A1:A6,">0")=6,2*A7,A7)

The COUNTIF function calculates how many of the cells in A1:A6 are greater than zero. If the count is six, then the formula doubles A7; if not, then just A7 is returned.

chuff
  • 3,504
  • No need for countif. Count() can be used like =IF(COUNT(A1:A6)=6,2*A7,A7) -- Count only counts numbers, not text. – teylyn Jan 30 '13 at 07:03
  • @teylyn: “COUNT only counts numbers, not text.”  True.   //   “No need for COUNTIF –– COUNT() can be used, as in =IF(COUNT(A1:A6)=6, …).  False.  Zero is a number.  If A1:A6 contains { 8, 8, 8, 8, 8, 0 }, COUNT(A1:A6) is 6, and timesheets often contain zeroes. – Scott - Слава Україні Feb 01 '13 at 22:19
  • Just for completeness(?), =IF(PRODUCT(A1:A6)>0, …) would work if we were guaranteed that all six cells have numbers in them –– but PRODUCT(A1:A6) gives the multiplication product (A1×A2×A3×A4×A5×A6) of the numbers in the range A1:A6, ignoring blanks and even text –– so, for example, if A1:A6 contains { 8, 8, 8, 8, 8, "off" }, PRODUCT(A1:A6) is 32768 (8×8×8×8×8).  So I guess =IF(AND(COUNT(A1:A6)=6, PRODUCT(A1:A6)>0), …) would work.  But chuff’s COUNTIF answer is clearly superior. – Scott - Слава Україні Feb 01 '13 at 23:04
0

It seems like you don't have a blank cell when no hours have been worked. If this was the case, you could simply use the IsBlank() function to check those cells.

Another way is to use a hidden column that uses an if formula to interrogate the hours worked for that day and inserts 1 if hours have been worked, and 0 if they haven't.

Then, in your formula that checks to see if the rate is double time, check that the sum of the hidden cells equals 6, which indicates that all days have been worked.

Edit: d'oh. forgot countif(). This works, but go with the other answer

mcalex
  • 2,471