Formula Rounding

innoin

New Member
Joined
May 4, 2017
Messages
33
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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>
 
Last edited:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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. ;)
 
Upvote 0
"=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...
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top