Excel Formulas

[RRCIT]

Excel 2013: I want a cell to be blank or have a value based on data/no data in a specific cell and data/no data in a range of cells.

So in cell AJ7 I want either a numerical value or no value.

Cell B7 has either a name or no name (however the cell has a formula associated with it). It gets populated with Names from another sheet in the workbook.

Cells C7:AG7 either have data (A, N, L, V, or H) or no data.

If Cell B7 has data and Cells C7:AG7 have no data: Cell AJ7 needs to have a numerical value of (.5).

If Cell B7 has data and Cells C7:AG7 have data: Cell AJ7 needs to have a value of (0).

If Cell B7 has no data and Cells C7:AG7 have no data: Cell AJ7 needs to have no value.

-----------------------------------------------------------------------------------------------------------------I can get a value in AJ7 if I use COUNTA for the range of Cells A7:AG7 but if I add a variable for Cell B7 (or Sheet2!B7) I generally get notified that the formula is not a formula. I have tried to add "AND" but no work.

-----------------------------------------------------------------------------------------------------------------Formulas that produce a value in AJ(x), but do not include the secondary variable --

=IF(COUNTA(C7:AG7)=0, ".5","0")

=IF(COUNTA('Employee Names'!B7)=0,"0",".5")

-----------------------------------------------------------------------------------------------------------------Formula with "AND" that does not work: (you've entered too many arguments)

=IF(AND(COUNTA('Employee Names'!B7="1")),C7:AG7,"0",".5")

[#2]

@RRCIT , it's too hard to understand what is the logic behind without the sample file. Regarding the last formula

=IF(AND(COUNTA('Employee Names'!B7="1")),C7:AG7,"0",".5")

Within it 'Employee Names'!B7="1" returns TRUE or FALSE, COUNTA on it will be always 1 since it always have one "not empty" argument.

Next, you have AND(1) which always return TRUE. Thus your formula is

=IF(TRUE,C7:AG7,"0",".5")

That doesn't work - you have 4 arguments instead of 3 required.

Perhaps you mean

=IF(AND( ('Employee Names'!B7=1), (COUNTA(C7:AG7)=1) ) ,0 ,0.5)

[#3]

@RRCIT 

This should work.

=IF(B7<>"",IF(COUNTA(C7:AG7)=0,0.5,0),"")

[#4]

I am adding my spreadsheet.

If there is no name in the Employee Name Column, then there would be no selections in C7:AG7 therefor there should be no value or a value of 0 in Perfect Attendance but the value should be hidden.

If there is a name in the Employee Name Column, and no selections in C7:AG7, then there should be a value of .5 in the Perfect Attendance Column.

If there is a name in the Employee Name Column, and either V, N, A, L, or H selected in C7:AG7, then there should be no value or a value of 0 in the Perfect Attendance Column and visible.

The premise here is: If they have perfect attendance for the month, they get a deduction of .5 from their "Total Accrued Points"

[#5]

@Detlef Lewin 

This formula works with the exception it causes a #VALUE! in the remaining rows columns AF/AG.

See the attached image. Also, I attached my workbook to the post...

[#6]

@Sergei Baklan 

I have already tried that formula and it doesn't work either. I have updated my post with additional info and a copy of the spreadsheet. Detlef Lewin provided a formula that does what I am looking for, but it breaks Columns AF/AG (causes a #VALUE!) to return into the empty cells.

[#7]

@RRCIT 

Now that I know you workbook the formula should be:

=IF(B7<>0,IF(COUNTA(C7:AG7)=0,0.5,0),"")

Using a direct reference to another worksheet is a very bad idea.

As for the #VALUE! error: You wanted a formula to produce a 'blank' which it does.

Your formula in 'January' column H is essentially:

=Number - AJ7

 But when AJ7 is 'blank" it is text. And Number-Text does not compute in Excel.

[#8]

@Detlef Lewin 

So, Can I make the value of AJ7 be 0 if there is no data in B7... 

I can then use conditional formatting to hide the 0....

[#9 · Accepted solution]

@RRCIT 

The revised formula for AJ7 would be:

=(COUNTA(C7:AG7)=0)*(B7<>0)*0.5

[#10]

@Detlef Lewin 

Excellent. Works Perfect. Don't even need to do Conditional Formatting. Than You.

[#11]

@Detlef Lewin 

The formula works great. Unfortunately, I have run into another issue. This needs to be conditional on an employee hire date. I have added a new column (Start Date) to the Employee Names sheet. Employees can't accrue Perfect Attendance points prior to their "Start Date". So if the employee starts in April... then Jan - Mar would not have points in the Perfect Attendance column.

[#12]

@RRCIT 

I don't understand the problem.

Perhaps you could add some data to illustrate the problem.

[#13]

@Detlef Lewin 

OK. I have entered Employee 1 and Employee 2:

Employee 1 Started 6/1/2108.

They get .5 points for each month that they have perfect attendance.

So 1.5 points total for Jan/Feb/Mar.

If they receive an attendance violation in Apr -- the violation gets reduced by the accrued perfect attendance points.

Employee 2 Started 3/1/2019.

They get .5 points for each month that they have perfect attendance.

They had perfect attendance in Mar.

So .5 points total for Mar.

If Employee 2 receives an attendance violation in Apr -- the violation gets reduced by the accrued perfect attendance points.

However, since employee 2 was not employed in Jan/Feb, they are not entitled to perfect attendance points for Jan/Feb.

So - we need to modify our formula so that it does not award perfect attendance points for the months that they weren't employed.

I have attached a spreadsheet with data. As you can see -- Empl 2 in April has accrued points of .5 (the same as Empl 1) however, it should be 1.5 since Jan/Feb should be 0 for Perfect Attendance.

Note that the workbook that is provided to my managers has most of the cells protected. The days have drop down selections and Employee Name/Points Brought Forward/Start Date are the only cells that are unprotected.

[#14]

@RRCIT 

=((COUNTA(C7:AG7)=0)*(B7<>0)*0,5)*(--(1&MonthName&CalendarYear)>=VLOOKUP([@[Employee Name]],'Employee Names'!$A$2:$C$3,3,FALSE))

[#15]

@Detlef Lewin 

I get an error: See attached.

[#16]

@RRCIT 

I attached the file.

[#17]

@Detlef Lewin 

OK. I see it seems to work however, I don't quite get this part: 'Employee Names'!$A$2:$C$3,3,FALSE)) ..

Can you break it down for me? I would think it should be $A$2:$C$2, for Employee 1 and

$A$3:$C$3, for Employee 2.... and what is the ,3, prior to the FALSE referring to?

Also, now there is a #N/A in the remaining cells for columns AI and AJ where there are no employees.

Not quite sure why my copy and paste didn't work....

[#18]

@RRCIT 

Also the months following the hire date April - Dec are not accruing perfect attendance points for Employee 2 and should be....

[#19]

This seems to be a perpetual conversation. I hereby admonish you to start a new one, given that you terminated this yourself.

[#20]

@RRCIT 

1. That is a simple VLOOKUP(). Did you never use a VLOOKUP() before? Take a look at the online help page.

2. When there is no employee then delete the row. Simple as that.

3. I was afraid this is getting more and more complex. With the current setup I will not be able to provide good solutions. Change your setup into a record list.