Multiple If Round Statements Based on Four Possibilities

[innoin]

Hello, I tried posting this question before, but didn't get any hits on it. I have an IF ROUND statement in a spreadsheet and I'm trying to extend it to four possible scenarios. I have one cell (on Tolerances!$B$27) that I update based on a length of time. The problem is that I might have 4 possible lengths of time (1, 5, 10, or 15). See my current formula below for a reference based on a 10 minute time span. If I change that tolerance cell to a 15, I'd like the formula to read that and calculate based on that amount of time (so it would run the second scenario I have below). And, as I said, I also need to incorporate a 1 and 5 minute span possibility. I've tried many combinations of this and can't get it to work once nested. Anyone have an idea of how to do this?

This is my current formula:
=IF(Tolerances!$D$37=10,IF(ISBLANK(T_Data!F12),NA(),IF(T_Data!F52<T_Data!F2,NA(),ROUND(IF(AND(T_Data!F12>=Tolerances!$B$27,T_Data!F12<Tolerances!$C$27),(T_Data!F12-T_Data!F2)/Tolerances!$D$37,NA()),1))))

This is an example I'd like to add for a second scenario:
=IF(Tolerances!$D$37=15,IF(ISBLANK(T_Data!F17),NA(),IF(T_Data!F52<T_Data!F2,NA(),ROUND(IF(AND(T_Data!F17>=Tolerances!$B$27,T_Data!F17<Tolerances!$C$27),(T_Data!F17-T_Data!F2)/Tolerances!$D$37,NA()),1))))

 

[Tetra201]

Here is an approach with a helper cell:

Put the following formula in a helper cell of your choice (say, X1):

=CHOOSE(MATCH(Tolerances!$D$37,{1,5,10,15},0),T_Data!F3,T_Data!F7,T_Data!F12,T_Data!F17)

Then, your formula becomes:

=IF(ISBLANK(X1),NA(),IF(T_Data!F52<T_Data!F2,NA(),ROUND(IF(AND(X1>=Tolerances!$B$27,X1<Tolerances!$C$27),(X1-T_Data!F2)/Tolerances!$D$37,NA()),1)))

 

[innoin]

Thanks for the quick help! Unfortunately I forgot to mention one critical piece of information...

I need to be able to auto fill down (to cell F1000) and to the right (all the way to cell BV1000), so I don't think that will work. All the "unlocked" (not sure what to call cells without the $ sign) will need to go up one digit as I go down to the next cell and on to the next column letter as I autofill to the right.

 

[innoin]

Here is an approach with a helper cell:

Put the following formula in a helper cell of your choice (say, X1):

=CHOOSE(MATCH(Tolerances!$D$37,{1,5,10,15},0),T_Data!F3,T_Data!F7,T_Data!F12,T_Data!F17)

Then, your formula becomes:

=IF(ISBLANK(X1),NA(),IF(T_Data!F52<T_Data!F2,NA(),ROUND(IF(AND(X1>=Tolerances!$B$27,X1<Tolerances!$C$27),(X1-T_Data!F2)/Tolerances!$D$37,NA()),1)))

Nevermind! I achieved what I was looking for by just creating a helper tab and autofilling that formula. I was apprehensive to do that since my worksheet is already 40MBs...and ridiculously slow, but it only added a couple more MB/s. Thanks for the help! That's a really cool formula and I'm already thinking of other things to use it on.