Shorting the Code

[Keith]

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

 

[Mike H]

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

 

[Jim Cone]

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

 

[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





- Show quoted text -

It works like a charm.

Thanks