I'm having trouble figuring out how to go about creating multiple possible rounding formulas. I'll try to transcribe what I want...
I'm trying to check heat-up rates for an oven so I start the formula with the "if(heat!e52<heat!e2,NA()" portion. That will give an NA if the temperature is going down (wish I could think of something that would work better than that...but my Excel skills are limited). The formula will be placed in the first cell and autofilled down for a possible 800 cells (each one representing one minute). Next, I want it to perform the calculation E3 minus E2 divided by a set amount of time (in this first part of the formula it would be "1" on the tolerance page as there's one minute between the two cells). Then I want it to round to the nearest tenth. It's the same formula four times. I will manually change cell TOL!$D$37 to either 1, 5, 10, or 15 and I want the corresponding formula to run based on this.
=IF(HEAT!E52<HEAT!E2,NA(),ROUND(IF(AND(HEAT!E3>=TOL!$B$27,HEAT!E3<TOL!$C$27,HEAT!E3<>"",TOL!$D$37=1),(HEAT!E3-HEAT!E2)/TOL!$D$37),1),ROUND(IF(AND(heat!E7>=Tol!$B$27,heat!E7<Tol!$C$27,heat!E7<>"",Tol!$D$37=5),(heat!E7-heat!E2)/Tol!$D$37),1),ROUND(IF(AND(heat!E12>=Tol!$B$27,heat!E12<Tol!$C$27,heat!E12<>"",Tol!$D$37=10),(heat!E12-heat!E2)/Tol!$D$37),1),ROUND(IF(AND(heat!E17>=Tol!$B$27,heat!E17<Tol!$C$27,Heat!E17<>"",Tol!$D$37=15),(heat!E17-heat!E2)/Tol!$D$37),1)))))
Thanks for any help!
I'm trying to check heat-up rates for an oven so I start the formula with the "if(heat!e52<heat!e2,NA()" portion. That will give an NA if the temperature is going down (wish I could think of something that would work better than that...but my Excel skills are limited). The formula will be placed in the first cell and autofilled down for a possible 800 cells (each one representing one minute). Next, I want it to perform the calculation E3 minus E2 divided by a set amount of time (in this first part of the formula it would be "1" on the tolerance page as there's one minute between the two cells). Then I want it to round to the nearest tenth. It's the same formula four times. I will manually change cell TOL!$D$37 to either 1, 5, 10, or 15 and I want the corresponding formula to run based on this.
=IF(HEAT!E52<HEAT!E2,NA(),ROUND(IF(AND(HEAT!E3>=TOL!$B$27,HEAT!E3<TOL!$C$27,HEAT!E3<>"",TOL!$D$37=1),(HEAT!E3-HEAT!E2)/TOL!$D$37),1),ROUND(IF(AND(heat!E7>=Tol!$B$27,heat!E7<Tol!$C$27,heat!E7<>"",Tol!$D$37=5),(heat!E7-heat!E2)/Tol!$D$37),1),ROUND(IF(AND(heat!E12>=Tol!$B$27,heat!E12<Tol!$C$27,heat!E12<>"",Tol!$D$37=10),(heat!E12-heat!E2)/Tol!$D$37),1),ROUND(IF(AND(heat!E17>=Tol!$B$27,heat!E17<Tol!$C$27,Heat!E17<>"",Tol!$D$37=15),(heat!E17-heat!E2)/Tol!$D$37),1)))))
Thanks for any help!