if statement with an ‘OR’ nested (xp)

Topic

New Reply

I am trying to create an if statement to do the following: if a certain cell(x) contains the value ‘0’ or text, the if statement should output zero. if those two situations dont occur, then it should divide that cell(x) by another in the worksheet and output that answer. does this make sense? please help at your earliest convenience!

Reply | Quote

Replies

For a simplified solution, try something like:

=IF(ISERROR(D32/A5),0,D32/A5)

Where D32 is the cell that may be text or zero and A5 is “another in the worksheet”. Please note that this will also produce 0 if “another in the worksheet” is either text or zero and D32 turns out to be a valid value.

If you want something that tests only D32, that can be designed also.

HTH

Reply | Quote

For a simplified solution, try something like:

=IF(ISERROR(D32/A5),0,D32/A5)

Where D32 is the cell that may be text or zero and A5 is “another in the worksheet”. Please note that this will also produce 0 if “another in the worksheet” is either text or zero and D32 turns out to be a valid value.

If you want something that tests only D32, that can be designed also.

HTH

Reply | Quote

Assuming that you are working with cells A1 and A2 with A2 as denominator try the below formula

=IF(AND(CELL(“Type”,A2)=”v”,A20),A1/A2,0)
Regards,
TD

Reply | Quote

Assuming that you are working with cells A1 and A2 with A2 as denominator try the below formula

=IF(AND(CELL(“Type”,A2)=”v”,A20),A1/A2,0)
Regards,
TD

Reply | Quote

both worked great. i can follow the first solution somewhat, but the second, i dont understand at all how it works? what is the ‘v’? do you mind explaining a little? thanks for your help!

Reply | Quote

The CELLS function in Excel returns information about, formatting, location, or contents of a specific cell or the upper-left cell in a referenced range. It can be a very useful function.

In the formula provided the info type requested is “Type” for cell A2. This returns the following values. “b” for Blank, “l” for labels and “v” for values. Since you only wanted to deal with values that are non zero the nested AND statement requires that the cell be a value AND that value not be zero for the formula to perform division; in all other cases (“b”,”l”, or the value zero) the formula returns -0- (zero).

Regards,

TD

Reply | Quote

The CELLS function in Excel returns information about, formatting, location, or contents of a specific cell or the upper-left cell in a referenced range. It can be a very useful function.

In the formula provided the info type requested is “Type” for cell A2. This returns the following values. “b” for Blank, “l” for labels and “v” for values. Since you only wanted to deal with values that are non zero the nested AND statement requires that the cell be a value AND that value not be zero for the formula to perform division; in all other cases (“b”,”l”, or the value zero) the formula returns -0- (zero).

Regards,

TD

Reply | Quote