If statements

[pdgarza]

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 Q9>0, 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,$q9>0),"(100%)","100%",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.

 

[Mike H]

Hi,

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

=IF(S9=0,1,IF(AND(R9=0,Q9>0),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 Q9>0) 100%
and (r9<>0 q9<0) 100%
r9<>0 s9/r9 as percentage


Mike

 

[pdgarza]

Thanks Mike! That worked perfectly!!

Hi,

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

=IF(S9=0,1,IF(AND(R9=0,Q9>0),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 Q9>0) 100%
and (r9<>0 q9<0) 100%
r9<>0 s9/r9 as percentage


Mike

 

[Mike H]

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