Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Help with my "if and" formulae and boolean values

Hi Guys,


Got an issue with a numbers spreadsheet that I'm making. It's a salary breakdown form for my workforce and I want to automate it as much as I can. One of the ways I am trying to do this is in regards to automatically calculating whether an employee has perfect attendance for a month in question.


How I do this is create a column for days taken off and a second column for whether perfect attendance is relevant to the employee in question as not all employees have this.


Now, if the first column has 0 (as in 0 days taken off) and the second column has "yes" (as in yes he is viable for perfect attendance) then he will automatically get an additional 200. My current formula is as such:


(if (and perfect attendance = yes, days taken off = 0),+200,+0)


The problem is that if the days taken off field is blank this also counts as 0 so the extra salary is given. Is there any way for the formula to only recognise the digit 0 as being true and not the blank field as well?


Thanks so much.

iPhone 7, iOS 10.2.1

Posted on

Reply
Question marked as Solved
Answer:
Answer:

If B2 is your number of days off and C2 is yes or no, then this should work:


IF(AND(B2=0,B2≠"",C2="yes"),200,0)

Posted on

Apr 23, 2018 5:58 AM in response to sheakun In response to sheakun

Hi sheakun,


Perhaps the ISBLANK function will help.

Type = in any spare cell to bring up the Function Browser. In the search box, type the first few letters of isblank to see this:


The ISBLANK function returns the boolean value TRUE if the specified cell is empty and the boolean value FALSE otherwise.


ISBLANK(cell)

cell: A cell reference to a single table cell. The referenced cell can contain any valueor be empty. If cell refers to more than one cell, ISBLANK will return the boolean value FALSE even if all cells are blank.


Notes

If the cell is completely blank (empty), the function returns the boolean value TRUE; otherwise it returns the boolean value FALSE. If the cell contains a space or a nonprinting character, the function will return FALSE, even though the cell appears to be blank.


Also, have a look at the LEN (length) function.


Happy Numbering!

Ian.

Apr 23, 2018 5:58 AM

Reply Helpful
User profile for user: sheakun

Question: Help with my "if and" formulae and boolean values