jeudi 25 juin 2020

Excel: Return logic test from IF statement

I have the following expression:

=0.85*$A1*0.1/15-0.04

where $A1 is a range in my sheet from 0, 1..., 15, so it looks like this in my sheet:

   A        B
1  0        =0.85*$A1*0.1/15-0.04
2  1        =0.85*$A2*0.1/15-0.04
.
.
.
16 15       =0.85*$A16*0.1/15-0.04

For some values the expression is <= 0, and since '0' is not a valid case for my expression, I want excel to return N/A() instead, so that these points wont show up when plotted later.

So I want to write an expression like the following:

=IF(0.85*$A1*0.1/15-0.04<=0;NA();0.85*$A1*0.1/15-0.04)

However, I don't want the logic test expression to appear twice in the IF statement, this double the maintenance of the expression, and also I want to avoid using an extra row for the intermediate calculation, since I want the structure to be scalable to other rows with different expressions.

I would rather write it in the lines of the following:

=IF(expression<=0;NA();expression), where expression = 0.85*$A1*0.1/15-0.04

Any clean and easy to maintain solutions for this?

Aucun commentaire:

Enregistrer un commentaire