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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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