Formula uses more levels of nesting than allowed

[Memphis317]

As you can see from the formula, I'm attempting to get point values from one time to the next in half-point increments. Not even halfway through the formula, I ran a test and it said there were too many levels of nesting. It will place the point values up to about the 9 point mark, but not afterwards. I need values from 10 to 1 showing half point values depending on the individual's run time. I don't want to create a chart with every second in between points and have it assign the value associated. That seems like it would take up a lot of space. Any suggestions?

=IF(P4>VALUE("01:00"),IF(P4<VALUE("11:46"),10,IF(P4>VALUE("11:45"),IF(P4<VALUE("11:57"),9.5,IF(P4>VALUE("11:56"),IF(P4<V ALUE("12:08"),9,IF(P4>VALUE("12:07"),IF(P4<VALUE("12:19"),8.5,IF(P4>VALUE("12:18"),IF(P4<VALUE("12:30"),8,IF(P4>VALUE("1 2:29"),IF(P4<VALUE("12:41"),7.5,IF(P4>VALUE("12:40"),IF(P4<VALUE("12:52"),7,IF(P4>VALUE("12:51"),IF(P4<VALUE("13:03"),6. 5,))))))))

 

[Derek Brown]

Hello and welcome to The Board.
You did not say which version of Excel is being used.
I would guess that it could be pre-2007 where the limit was 7 nested IF statements.
At 2007+ it would be 64.
When trying your formula in 2016, the formula generated a warning:
It would seem that there were some brackets missing at the end.
The corrected formula had eight more ")" at the end.
Try the corrected formula first and let me know if that solves your problem (assuming that you are using Excel 2007 or later).

 

[Derek Brown]

Will resubmit this later

 

[FDibbins]

=IF(P4>VALUE("01:00"),IF(P4<VALUE("11:46"),10,IF(P4>VALUE("11:45"),IF(P4<VALUE("11:57"),9.5,IF(P4>VALUE("11:56"),IF(P4<V ALUE("12:08"),9,IF(P4>VALUE("12:07"),IF(P4<VALUE("12:19"),8.5,IF(P4>VALUE("12:18"),IF(P4<VALUE("12:30"),8,IF(P4>VALUE("1 2:29"),IF(P4<VALUE("12:41"),7.5,IF(P4>VALUE("12:40"),IF(P4<VALUE("12:52"),7,IF(P4>VALUE("12:51"),IF(P4<VALUE("13:03"),6. 5,))))))))

1. If you start at the highest and work down, you can cut down on the IF's
2. Looks to me like there is a mathematical regression there?
10-(11:46-0:01)/2
something like that?

 

[nikio8]

Would you be able to use vlookup table with TRUE ?
Sort your data nicely and set second column.

=VLOOKUP(F9,$B$7:$C$16,2,TRUE)

[TABLE="width: 330"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1.25[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1.75[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.25[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.75[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.25[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]