Formula Rounding

[innoin]

Hello, I've got a problem with a couple formulas I've entered on a spreadsheet. The first formula is performing a calculation based on data from another tab. I want it to round the results to the nearest whole number. I can click "Decrease Decimal" and it shows 1.9 as 2 for example. The problem is that I'm referencing this cell (actually it's an entire column) on another tab for a "pass/fail" state. I can't get it to recognize the result as passing if it's 1.9. I've tried incorporating a rounding formula but can't seem to get it to work. I also have these cells on a graph and the 1.9 will show below the minimum on the graph as well.

This is the formula doing the calculation:

=IF(ISBLANK(Data!E12),NA(),IF(Data!E22<Data!E2,NA(),IF(AND(Data!E12>=130,Data!E12<190),(Data!E12-Data!E2)/10,NA())))

This is the formula I have referencing that calculation for the pass/fail condition:

=IF(ISNA(Heat!C2), "Removed", IF(AND(2<=Heat!C2,Heat!C2<=8) = TRUE, "Pass", "Fail"))

Can any of you Excel wizards help me!?

Thanks

 

[petertenthije]

The highlighted bit is wrong.
=IF(ISBLANK(Data!E12),NA(),IF(Data!E22<data!e2,na(),if(and(data!e12>=130,Data!E12<190),(Data!E12-Data!E2)/10,NA())))


What are you trying to do there? Do you need E22 to be 130 and E12 to be less then 190? In that case you need to add a AND to your formula. Note that I removed a closing bracket on the tail-end of the formula.
=IF(ISBLANK(Data!E12),NA(),IF(and(Data!E22<data!e2,na(),if(and(data!e12>=130,Data!E12<190),(Data!E12-Data!E2)/10,NA()))


As for adding a rounding to 0 decimals, add:
=IF(ISBLANK(Data!E12),NA(),IF(and(Data!E22<data!e2,na(),if(and(data!e12>=130,Data!E12<190),ROUND((Data!E12-Data!E2)/10,0),NA()))</data!e2,na(),if(and(data!e12>

</data!e2,na(),if(and(data!e12></data!e2,na(),if(and(data!e12>

 

[innoin]

Hmmm...I have no idea where I copied that first formula from! I must have sent a cell I was trying to figure out...the actual first formula is this:

=IF(ISBLANK(Data!E12),NA(),IF(Data!E22<Data!E2,NA(),IF(AND(Data!E12>=130,Data!E12<190),(Data!E12-Data!E2)/10,NA())))

I want it to perform the calculation (E12 minus E2 divided by 10) if e12 is greater than 130 and less and 190. I really don't know where I got that that random formula...sorry about that! Glad you were able to figure it out anyway though. That worked perfectly! I wasn't sure where to put that "Round" or the zero. Thanks for the help.

 

[innoin]

Um...hmmm...apparently I'm supposed to put parenthesis or something around my formula when posting. That just chopped out half my formula when I clicked post! Well...anyway, your addition did work so thanks again!

 

[petertenthije]

No problem!

Just to be sure, you noticed you are comparing E22 (E-twentytwo) to 130 and E12 (E-twelve) to 190? It's an easy mistake to make, but can have, shall we say, interesting consequences.

 

[innoin]

"=IF(ISBLANK(TC_Data!E17),NA(),IF(TC_Data!E22<TC_Data!E2,NA(),IF(AND(TC_Data!E17>=130,TC_Data!E17<220),(TC_Data!E17-TC_Data!E2)/15,NA())))"

Let's see if that works. Thanks for noting that, but random pieces of the formula seem to have disappeared upon posting it...

 

[innoin]

Lol okay, that didn't work either! I need to figure that out in the future when I have a question. The formula I'm pasting before clicking "Post" is completely different from what is actually posting. That E22 comes much earlier and isn't related to the E12. Thanks for noting that though!