Column Setup as True/False if Cell Reference Has Name In it...

[cwangosu]

This should be a relatively easy question.

I have a column (B) that I want to display these values based off
column (A):

1) If Column A is blank, Column B is blank.
2) If Column A has something, it should be set at 0.
3) If Column A has something, it may be changed to 1.

1 and 2 are what I'm worried about.

This is essentially used to give a percent complete. There will be
names in Column A and if it's 0, it means they didn't complete their
assignment. If there is no name in Column A, then the field should be
blank and not affect what the AVERAGE function used at the bottom of
Column B to determine the percent complete.

 

[Ragdyer]

If I understand what you're saying, you will place a "1" in column B when
you receive the completed assignment.

*Until* you do so, you just want column B to display zero if the
corresponding row in column A contains a name, and display "nothing" if
column A is empty.

=IF(A1="","",0)

 

[Max]

Not really sure, but here's one play at it ..

Assume you have in col A (Names) in A1:A4
data from row2 down, i.e.:

Names
ABC
DEF
XYZ

Put in B2: =IF(ISBLANK(A2),"",IF(A2=0,0,1))
Copy down

Put in say B5: =AVERAGE(B2:B4)

Put in B6: =AVERAGE(IF(B2:B4<>0,B2:B4))
Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

B5 and B6 will both return "1"

Now clear cell A2 ("ABC") to make it blank
(use Delete key)
(this simulates "No name in col A")

B5 and B6 both return "1"

Now type a zero in A2, press ENTER
(this simulates "did not complete assignment in col A")

B5 returns : "0.66667" (2 divided by 3)
B6 returns : "1" (2 divided by 2)

The array AVERAGE() formula in B6 will
disregard zero values within the range
while the simple AVERAGE() in B5 will not