Forum Discussion
Excel Formulas
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")
21 Replies
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"
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...
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.
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)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.
