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

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

Test this one...
=IF(C5<20001,6,(ROUNDDOWN(C5/20000,0))*6+6)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Keith"
wrote in message
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

On Feb 5, 3:08*pm, Mike H wrote:
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- Hide quoted text -

- Show quoted text -

It works like a charm.

Thanks