IF greater than negative value counts blanks as zero

[fransden]

Hi again guys,

Thanks for beeing so helpful.

Now I have another problem. This IF formula here is counting blank cells as zero (since the percentile of the data in the if formula is a negative value blanks=zero which is above the negative value and is therefore returned).

=(IF('Monthly Performance'!B21>=PERCENTILE('Monthly Performance'!$B$21:$AN$21,0.7),(((1+'Monthly Performance'!B21))^(1)-1),""))

Anyone got a solution to this? I would be very grateful!

Please see attached.

/Frans

[fransden]

Never mind, i sorted it out.

[Pepe Le Mokko]

Care to share your solution with the board?

[fransden]

Of course. I combined the fomula with an isblank function, as follows:

=IF(ISBLANK('Monthly Performance'!B254)=TRUE;"";IF('Monthly Performance'!B254>=PERCENTILE('Monthly Performance'!$B254:$QB254;0.7);(((1+'Monthly Performance'!B254))^(1)-1);""))

[Ace_XL]

A tad shorter..

=IF('Monthly performance'!B254="";"";IF('Monthly performance'!B254>=PERCENTILE('Monthly performance'!$B254:$Q254;0.7);(1+'Monthly performance'!B254)^1-1;""))