• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

blank the cell if values above 7.5% or below minus 7.5%

D

Don Preston

New Member
Cells in Row 15 should populate only if the formula results in a value of less than minus 7.5% -or- greater than a value of plus 7.5%. That’s a range of 15%. Values occurring within this range should result in a blank cell.

If(P14>7.5%,P14,””) This works if the value is above plus 7.5%. What should it look like to include any value past minus 7.5% ?

Excel 2010 Windows 7

I've got Walkenbachs book, but I'm lost! Thanks.
 
Hi, Don Preston!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, an alternative for the condition is this:
IF(ABS(P14)<=7.5,"",P14) -----> wrong
IF(ABS(P14)<=0.075,"",P14) -----> correct
IF(ABS(P14)<=7.5%,"",P14) -----> correct
or tweaking Somendra Misra's formula, inverting the true and false clauses, otherwise it won't work:
IF(AND(P14>=-7.5,P14<=7.5),"",P14) -----> wrong
IF(AND(P14>=-0.075,P14<=0.075),"",P14) -----> correct
IF(AND(P14>=-7.5%,P14<=7.5%),"",P14) -----> correct

Regards!

@Somendra Misra
Hi!
I think you've inverted the IF clauses, am I wrong?
Regards!

EDITED

PS: Formulas fixed, thanks to Debraj(ex-Roy).
 
Last edited:
The value in P14 is minus 3%. Using this formula results in P15 (cell where formula is placed) showing -3%. The cell should be blank as it is not greater than -7.5%. I removed the = sign to fit the need. Thanks.
 
Hi, Don Preston!
Have you tested it with my 1st choice formula? It'd display a blank.
Regards!
 
@Debraj(ex-Roy)
Hi, buddy!
You're right, I wrote 7.5 and I should have written 0.075 or 7.5%. Thanks for the catch, gonna update original post.
Regards!
 
Hi, Don Preston!
Glad you solved it. But please check Debraj(ex-Roy)'s last post, regarding de 7.5 vs .0075 or 7.5% values.
Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
@Don Preston ...

Don Preston said:
in a value of less than minus 7.5% -or- greater than a value of plus 7.5%. That’s a range of 15%.
Click to expand...

Value less than -7.5% will be -7.6, -7.7, -8 and so on so how u will get range of 15%. I think you mean to say
-7.5<= Value <=7.5%. Is this the case?

@SirJB7 ....Now consider my formula.

Regards!
 
So far, can't get them to display values below -7.5% or values above 7.5% in the cell. The cell will blank out if the reporting cell is only -3%. But, it also blanks out if the reporting cell is +8%. Forget the = entry. 7.5 % results in a blank. Thanks.
 
Anything out of the range of -7.5 and below, and +7.5 and above on the x or y axis should show up (in big red, capitalized values). Everything else is within the acceptable 15 point range. Thank you.
 
Hi, Don Preston!
Give a look at the uploaded file and tell us which is the wanted output, from columns Q:R.
Regards!

@Somendra Misra
Hi!
In the file there're the 3 formula versions, your original, my original, which are both opposite, and yours with the clauses inverted. Columns Q:R, for P values ranging from -10% to 10%. Let us see what the OP tried to mean.
Regards!
 

Attachments

Hi, Don Preston!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!

@Somendra Misra
Hi!
Waiting for my six pack of Carlsberg... ;) You invite.
Regards!
 
You must log in or register to reply here.
Back
Top