why only when i select company=A all summary row highlighted work fine ?

[paulyeo11]

Hi All

I have a QV doc , which give me very funny issue. which i am not able to figure out ! That is when i filter company = A , it work fine , because all summary row is highlighted with yellow and is working fine .But when i select all other company not working fine.

Below  image shown how the excel file control which row i wanted to high lighted which c fill in the cell.

Hope some one can give me hints how to trouble shoot this .

Paul

[sebastiandperei · Accepted solution]

Sorry Paul, I was mistaked.

I guess you have used this "if" because of you want to show 100% when the Column(1) =0 AND Column (2) <>0. If you just do the first condition, it returns a value also if the second one is 0 too, and this is the cause of the "extra" rows.

You should use the following exp:

=If(Column(1)=0 and Column(2)<>0,1,(Column(2)-Column(1))/(Column(1)))

Please, try it and tell us!

(the first suggestion, wasn't useful for you?)

[paulyeo11]

Hi Sebastain

Many thank for your help , when i try column(1)/column(2) , the extra row which i don't want disappear.

So now working fine.

Paul

[paulyeo11]

Hi AnTo

Thank you for your expression.

Paul

[paulyeo11]

Hi Sebastian

Since you are the so good in QV :-

Re: P&amp;L Table % change work for sales row but not work for exp row.

I have one more question on above link , hope you can help me again.

Paul

[sebastiandperei]

Paul, take in mind that if you only use Column (1) / Column (2), instead of

=If(Column(1)=0 and Column(2)<>0,1,(Column(2)-Column(1))/(Column(1)))

If you have a zero in Column (1), but not zero in Column (2), you wont get variance, and it would be. For this reason, you should use the second expression.

Bye!