Forum Discussion

RRCIT's avatar
RRCIT
Copper Contributor
Apr 25, 2019
Solved

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.  ...
excel
Formulas and Functions
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Apr 26, 2019

    RRCIT 

     

    The revised formula for AJ7 would be:

    =(COUNTA(C7:AG7)=0)*(B7<>0)*0.5
RRCIT's avatar
RRCIT
Copper Contributor
Apr 25, 2019

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...

 

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Apr 26, 2019

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.

 

  • RRCIT's avatar
    RRCIT
    Copper Contributor
    Apr 26, 2019

    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....

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Apr 26, 2019

      RRCIT 

       

      The revised formula for AJ7 would be:

      =(COUNTA(C7:AG7)=0)*(B7<>0)*0.5
      • RRCIT's avatar
        RRCIT
        Copper Contributor
        Apr 30, 2019

        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.

Resources