I am having a lot of trouble with an if, then statement. Here is what I have:

In the following cells I will have a number
Q9, R9, S9

In T9 I am wanting it to calculate the following:
If S9=0, then it should automatically equal 00.00%, otherwise
If R9=0, and Q90, thenit should equal (100%), if this statement is false,
then it should equal 100%, otherwise
If R<0, then S9/R9

This is the function I entered:
=IF($S9=0,"00.00%",if(and($r9=0,$q90),"(100%)","1 00%",if($r9<0,$S9/$R9)))

We are working on these very detailed reports and this is something our
supervisor asked us to work on. I'm not sure if this can even be done, but I
am stuck at this point.

Hi,

If i've managed to follow you logic this may work. Format the cell as %.

=IF(S9=0,1,IF(AND(R9=0,Q90),1,IF(AND(R9<0,Q9<=0) ,1,IF(R9<0,S9/R9,""))))

If this is incorrect correct the table below and we can try again

condition output
s9=0 0%
and (r9=0 Q90) 100%
and (r9<0 q9<0) 100%
r9<0 s9/r9 as percentage


Mike

"pdgarza" wrote:

I am having a lot of trouble with an if, then statement. Here is what I have:

In the following cells I will have a number
Q9, R9, S9

In T9 I am wanting it to calculate the following:
If S9=0, then it should automatically equal 00.00%, otherwise
If R9=0, and Q90, thenit should equal (100%), if this statement is false,
then it should equal 100%, otherwise
If R<0, then S9/R9

This is the function I entered:
=IF($S9=0,"00.00%",if(and($r9=0,$q90),"(100%)","1 00%",if($r9<0,$S9/$R9)))

We are working on these very detailed reports and this is something our
supervisor asked us to work on. I'm not sure if this can even be done, but I
am stuck at this point.

Thanks Mike! That worked perfectly!!

"Mike H" wrote:

Hi,

If i've managed to follow you logic this may work. Format the cell as %.

=IF(S9=0,1,IF(AND(R9=0,Q90),1,IF(AND(R9<0,Q9<=0) ,1,IF(R9<0,S9/R9,""))))

If this is incorrect correct the table below and we can try again

condition output
s9=0 0%
and (r9=0 Q90) 100%
and (r9<0 q9<0) 100%
r9<0 s9/r9 as percentage


Mike

"pdgarza" wrote:

I am having a lot of trouble with an if, then statement. Here is what I have:

In the following cells I will have a number
Q9, R9, S9

In T9 I am wanting it to calculate the following:
If S9=0, then it should automatically equal 00.00%, otherwise
If R9=0, and Q90, thenit should equal (100%), if this statement is false,
then it should equal 100%, otherwise
If R<0, then S9/R9

This is the function I entered:
=IF($S9=0,"00.00%",if(and($r9=0,$q90),"(100%)","1 00%",if($r9<0,$S9/$R9)))

We are working on these very detailed reports and this is something our
supervisor asked us to work on. I'm not sure if this can even be done, but I
am stuck at this point.

well in equal measure I'm both glad I could help and amazed :)

"pdgarza" wrote:

Thanks Mike! That worked perfectly!!

"Mike H" wrote:

Hi,

If i've managed to follow you logic this may work. Format the cell as %.

=IF(S9=0,1,IF(AND(R9=0,Q90),1,IF(AND(R9<0,Q9<=0) ,1,IF(R9<0,S9/R9,""))))

If this is incorrect correct the table below and we can try again

condition output
s9=0 0%
and (r9=0 Q90) 100%
and (r9<0 q9<0) 100%
r9<0 s9/r9 as percentage


Mike

"pdgarza" wrote:

I am having a lot of trouble with an if, then statement. Here is what I have:

In the following cells I will have a number
Q9, R9, S9

In T9 I am wanting it to calculate the following:
If S9=0, then it should automatically equal 00.00%, otherwise
If R9=0, and Q90, thenit should equal (100%), if this statement is false,
then it should equal 100%, otherwise
If R<0, then S9/R9

This is the function I entered:
=IF($S9=0,"00.00%",if(and($r9=0,$q90),"(100%)","1 00%",if($r9<0,$S9/$R9)))

We are working on these very detailed reports and this is something our
supervisor asked us to work on. I'm not sure if this can even be done, but I
am stuck at this point.