# Multiple Round If Statements

#### innoin

##### New Member
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### innoin

##### New Member
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!

Replies
6
Views
372
Replies
3
Views
86
Replies
3
Views
75
Replies
9
Views
52
Replies
1
Views
154