Or even...

=VLOOKUP(H4,D1:F9,if(b40,2,3),1)


Biff wrote:

This might be confusing:

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3 ,1))
Where D1:E9 is the above table.

Those table ranges, D1:E9 and D1:F7, are based on the unequal number of
entries in each column But I'm guessing that it's incomplete just because
you ran into nesting problems.

To make it less confusing just change it to: (and finish your table)

=IF(B40,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3, 1))

Biff

"Biff" wrote in message
...
I don't know exactly what you're doing but you need to create a table. This
table is based on your bottom formula:

0............0..........0
124.....0.45.....0.35
149.....0.55.....0.45
199.....0.7.......0.55
249.....0.9.......0.7
299.....1.05.....0.85
349.....1.25.....1
399.....1.4
499.....1.6

The 2nd column would be used when B40 and the third column would be used
when B4<=0. (at least, that's the logic of your IF formulas)

=IF(B40,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3, 1))

Where D1:E9 is the above table.

Biff

"Loudmouth" wrote in message
...
I need about 25 to make it work. Can you suggest another way of comparing
that many numbers?

"Biff" wrote:

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" wrote in message
...
This formula works:
=IF(B40,IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1. 05,IF(H4249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45)))))),IF(H4349,1,IF(H4299,0.85,IF (H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H412 4,0.35,0)))))))

This formula does not, why?
=IF(B40,IF(H4 449, 1.6,
IF(H4399,1.4,IF(H4349,1.25,IF(H4299,1.05,IF(H4 249,0.9,IF(H4199,0.7,IF(H4149,0.55,
IF(H4124,0.45))))))),IF(H4349,1,IF(H4299,0.85,I F(H4249,0.7,IF(H4199,0.55,IF(H4149,0.45,IF(H41 24,0.35,0)))))))






--

Dave Peterson