Apr 25 2019 01:14 PM
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")
Apr 25 2019 01:44 PM
@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)
Apr 25 2019 01:45 PM
Apr 25 2019 01:59 PM
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"
Apr 25 2019 02:22 PM
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...
Apr 25 2019 02:27 PM
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.
Apr 25 2019 09:07 PM
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.
Apr 26 2019 10:37 AM
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....
Apr 26 2019 11:57 AM
SolutionApr 26 2019 02:09 PM
Excellent. Works Perfect. Don't even need to do Conditional Formatting. Than You.
Apr 30 2019 09:56 AM
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.
Apr 30 2019 11:22 AM
Apr 30 2019 01:33 PM
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.
May 01 2019 02:16 AM
=((COUNTA(C7:AG7)=0)*(B7<>0)*0,5)*(--(1&MonthName&CalendarYear)>=VLOOKUP([@[Employee Name]],'Employee Names'!$A$2:$C$3,3,FALSE))
May 01 2019 06:58 AM
May 01 2019 10:01 AM
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....
May 01 2019 10:11 AM
Also the months following the hire date April - Dec are not accruing perfect attendance points for Employee 2 and should be....
May 01 2019 10:47 AM
May 01 2019 11:04 AM
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.
Apr 26 2019 11:57 AM
Solution
The revised formula for AJ7 would be:
=(COUNTA(C7:AG7)=0)*(B7<>0)*0.5