formula explanation

H

Hoops

I am trying to adjust a formula to include more cells...I don't understand
the formula that well. It's for a employee work schedule. I've changed it
to go from hourly to every half hour. If there is text within certain cells
then it should equal a half hour of work for that employee. The formula
right now based on hourly is: =SUM(IF(ISTEXT(C6:K7),1,0)) The schedule goes
from 7am to 3pm (example c5 is 7am, then d5 is 8am and so on until 3pm) I
want to change: c5 to 9am, d5 to 9:30, e5 to 10:00 and so on all the way to
7pm. If there is any text within c6 and w6 (which would be 7pm) then I want
the formula to give me total hours worked.
 
L

Luke M

Since each now only represents half an hour, we'll change the counter from 1
to 0.5:
=SUMPRODUCT(0.5*ISTEXT(C6:W6))

Note that the previous formula was an array formula, while this one is not.
 
J

Jim Thomlinson

That is an array formula. You will want it to look like this

=SUM(IF(ISTEXT(C6:W6),0.5,0))

As an array formula it must be committed with Ctrl+Shift+Enter and not just
Enter. Once entered XL will automatically add {} around the formula to
indicate that it is an array formula.
 
B

Bernard Liengme

This formulas begins by counting how many cells in the range C6:K7 have text
(not numbers) in them. If the answer is more than zero, the formula returns
1 otherwise it returns 0.

I suspect you mistyped that and want only one row:
=SUM(IF(ISTEXT(C6:K6),1,0)) (K6 not K7)

To cover more cells use =SUM(IF(ISTEXT(C6:W6),1,0))
Now here is the critical part. This is an ARRAY formula so you must commit
it with CTRL+SHIFT+ENTER not just ENTER

An alternative formula would be =COUNTA(C6:W6)-COUNT(C6:W6). This is NOT an
array formula. If counts how many cells are not empty and subtracts how many
have number values.

best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top