Multiple Round If Statements

innoin

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

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

innoin

New Member
Joined
May 4, 2017
Messages
33
Hello again! I think I was a bit too obtuse in my explanation of what I'm trying to do. And I included a formula I had been trying to alter instead of the original working formula, so I'll try again! I'll also eliminate the part of the formula that I don't need help with.

The formula says that if it's between two temperatures noted on a tolerance tab (in "Tol!$D$20"), then perform the calculation (E12 minus E2 divided by the amount of time between E12 and E2 which is 10). I want it to do this for 4 possible amounts of time though (1, 5, 10, and 15). If I change the tolerance cell D20 to 5, then I want it to check everything to"Temp!E7" instead of "Temp!E12" and then run the E7-E2/Tol!$D$20 formula. The cell will be autofilled down and I need it to go to E13-E3 for the next minute and E14-E4 and so on for the 10 minute span, so I can't put those in as tolerances (as far as I know). So! I'm just trying to create the same formula four times based on what I enter in the "Tol!$D$20" cell, but with a different formula for each condition. I just can't seem to create an if statement that allows me to combine them all and only run the portion based on the condition of the D20 cell.

Two examples:

=ROUND(IF(AND(Temp!E12>=Tol!$B$5,Temp!E12<Tol!$C$5),(Temp!E12-Temp!E2)/Tol!$D$20,NA()),1))) this would be the formula for a 10 minute span

=ROUND(IF(AND(Temp!E7>=Tol!$B$5,Temp!E7<Tol!$C$5),(Temp!E7-Temp!E2)/Tol!$D$20,NA()),1))) this would be the formula for a 5 minute span

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,936
Messages
5,545,104
Members
410,656
Latest member
Hydraulics
Top