View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Mike H Mike H is offline
external usenet poster
 
Posts: 11,501
Default Shorting the Code

Keith,

Somewhere out of the way create a table like this. Note the left column is
sorted and in my case in columns A & B

Col A Col B
0 6
40000 12
60000 18
80000 24
100000 30

Then use the formula
=VLOOKUP(C5,A1:B5,2,TRUE)
Make the table the size uo want and alter the formula range to match that
table size

Mike

"Keith" wrote:

Can this code be shortened? It's goes up by a fixed amount everytime
so i thought there might be a way. I have to go all the way up to
1,000,000...thats why i'm asking.

=IF($C$5<20001,6,IF($C$5<40000,12,IF($C$5<60000,18 ,IF($C
$5<80000,24,IF($C$5<100000,30,IF($C$5<120000,36,IF ($C$5<140000,42,IF($C
$5<160000,48,0))))))))

Thanks for the help,
Keith