A function that only shows the value if the value is in a certain numerical range

[Brennen81]

Hello,

I have cells that contain a formula that could equate to values of quite simply anything, positive or negative.

I was wondering if there was a function that could be inserted that would have the cell only display the value if the formula brought back a value between 0 and 1. It is ok if an error value came up instead.

This is a sample of the formula

=(((P$5-B$5)/B$5)+((P$6-B$6)/B$6)+((P$7-B$7)/B$7)+((P$8-B$8)/B$8)+((P$9-B$9)/B$9)+((P$10-B$10)/B$10)+((P$11-B$11)/B$11)+((P$12-B$12)/B$12)+((P$13-B$13)/B$13)+((P$14-B$14)/B$14)+((P$15-B$15)/B$15)+((P$16-B$16)/B$16)+((P$17-B$17)/B$17)+((P$18-B$18)/B$18)+((P$19-B$19)/B$19)+((P$20-B$20)/B$20)+((P$21-B$21)/B$21)+((P$22-B$22)/B$22)+((P$23-B$23)/B$23)+((P$24-B$24)/B$24))/((((P$5-B$5)/B$5)+((P$6-B$6)/B$6)+((P$7-B$7)/B$7)+((P$8-B$8)/B$8)+((P$9-B$9)/B$9)+((P$10-B$10)/B$10)+((P$11-B$11)/B$11)+((P$12-B$12)/B$12)+((P$13-B$13)/B$13)+((P$14-B$14)/B$14)+((P$15-B$15)/B$15)+((P$16-B$16)/B$16)+((P$17-B$17)/B$17)+((P$18-B$18)/B$18)+((P$19-B$19)/B$19)+((P$20-B$20)/B$20)+((P$21-B$21)/B$21)+((P$22-B$22)/B$22)+((P$23-B$23)/B$23)+((P$24-B$24)/B$24))-(((P$26-B$26)/B$26)+((P$27-B$27)/B$27)+((P$28-B$28)/B$28)+((P$29-B$29)/B$29)+((P$30-B$30)/B$30)+((P$31-B$31)/B$31)+((P$32-B$32)/B$32)+((P$33-B$33)/B$33)+((P$34-B$34)/B$34)+((P$35-B$35)/B$35)+((P$36-B$36)/B$36)+((P$37-B$37)/B$37)+((P$38-B$38)/B$38)+((P$39-B$39)/B$39)+((P$40-B$40)/B$40)+((P$41-B$41)/B$41)+((P$42-B$42)/B$42)+((P$43-B$43)/B$43)+((P$44-B$44)/B$44)+((P$45-B$45)/B$45)))


Thanks so much all.

Brennen

[bdicarlo1]

Do you mean something like this?

=IF(OR((SUM(P5:P45)-SUM(B5:B45))/SUM(B5:B45)=0,(SUM(P5:P45)-SUM(B5:B45))/SUM(B5:B45)=1),(SUM(P5:P45)-SUM(B5:B45))/SUM(B5:B45),"")

[6StringJazzer]

Do you mean something like this?

=IF(OR((SUM(P5:P45)-SUM(B5:B45))/SUM(B5:B45)=0,(SUM(P5:P45)-SUM(B5:B45))/SUM(B5:B45)=1),(SUM(P5:P45)-SUM(B5:B45))/SUM(B5:B45),"")

Unfortunately that expression is not arithmetically equivalent to the original formula. Also, the request was to show a value if it's between 0 and 1, but this formula shows it only when it is exactly equal to 0 or 1.

[Brennen81]

=IF(AND(VLOOKUP($B7,rowcounter,K$1,FALSE)<1,VLOOKUP($B7,rowcounter,K$1,FALSE)>0),VLOOKUP($B7,rowcounter,K$1,FALSE), "---")

This worked for me. All the values are brought to an adjacent sheet. Now they display the value if it is between 0-1, otherwise it returns ---

Thank you all. Please do understand my gratitude and appreciation for taking the time to help

[6StringJazzer]

The easiest way to do that is to put your big formula in one cell, let's say A1, then put this formula where you want the final result:

=IF(AND(0<=A1,A1<=1),A1,"")

Otherwise you have to repeat that whole formula three times within the IF statement, everywhere you see A1 above.