{=SUM (IF (ISERR (- $H4: $AK4), and the IF (-- TEXT ($H4: $AK4, "0;; 0; \ 0 ") & lt; 8, $H4: $AK4/8, 1)))}
Explain that this is a calculation attendance EXCEL spreadsheet, the H4: this area AK4, preserved the 31 days of attendance record, record format, the day's work time, the unit is the hour, and all integer, such as over 8 hours, just fill in the number 8, fill in the 4, 4 hour if personal leave sick leave or day off, just with different symbols instead of (such as rectangle, triangle, or letters, etc.),
The use function of cell, the return value, is the current row of this staff, corresponding to the number of days a month to go to work,
Days rule is that if the work time is greater than or equal to 8 hours in the day, 1 day, less than 8 hours, not one day, is divided by eight days to get to work with the actual working hours, 4 hours, for example, is 0.5 days, 3 hours, is 0.375 days, eight hour a day, 10 hour a day, etc.,
I can now understand layer IF the function, but the IF function condition of outer ISERR (), is not very understand, the only parameters, especially ISERR inside contain the meaning of the symbol "-" is not clear, please expert guidance. Is - $H4: $AK4, minus sign is what mean?
CodePudding user response:
$AK4 refers to AK4 absolute reference cell, you bring the company to other cell replication, is still used in the formula to calculate AK4 cell Numbers,- $H4 not too clear, continue to wait for others to solve;
CodePudding user response:
IS [b] ERR of the return value IS A Boolean, as IF the judgment of the conditions, ISERR IS to check whether A value to # N/A error, such as symbols, such as the box you write directly=ISERR cell (symbols), the return value IS FALSE, IF add A "-", the return value IS TRUE, in this case, IF the line of symbols, ISERR return value to TRUE, the IF function condition was set up to return to the first value, namely IS empty; The line without symbols, ISERR return value to FALSE, IF conditions are not set up returns the second value, calculated again, therefore, have the symbols, also have to artificial calculation