Yes, that works. What does the -3 accomplish?

This section of the formula: INT(E13/50000)
calculates how many whole multiples of 50,000's
are in cell E13's value.

125,000 has 2...(actually 2.5, the INT truncates the fractions)
150,000 has 3.

But...you want to start calculating beginning with 200,000.

200,000 has 4...so by subtracting 3 we effectively discard
values below 200,000

(The MAX function makes sure the values don't go negative.)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Donna" wrote in message
...
Yes, that works. What does the -3 accomplish?

"Ron Coderre" wrote:

If I understand what you're asking...you want the lower limits to be
inclusive.
So...200,000 would return: 100....instead of 0.

If that's true, for 50k increments, try this:
F13: =MIN((MAX(INT(E13/50000)-3,0))*100,900)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Donna" wrote in message
...
One other question. Can this formula work for 200,000 or more, 300,000
or
more, etc.?

"Ron Coderre" wrote:

You're welcome, Donna.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)

"Donna" wrote in message
...
Thanks, Ron. The second formula works work for what I need.

"Ron Coderre" wrote:

With
E13: (a value)

....and...assuming your example is a simplified version of what you
really
want...

Try this:

With this list in A1:B10
200,000 0
250,000 100
300,000 200
350,000 300
400,000 400
450,000 500
500,000 600
550,000 700
600,000 800
999,999 900

This formula finds E13 in Col_A and returns the corresponding Col_B
value
:
F13: =INDEX(B1:B10,MATCH(1,FREQUENCY(E13,A1:A9),0))

BUT.....If your example is exactly what you want...
try this:
F13: =MIN((MAX(CEILING(E13/50000,1)-4,0))*100,900)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Donna" wrote in message
...
I need to add another level (if over 600,000 then 900) to this
formula
and
when I do I get an error that the formula is over the level of
nesting
allowed. Got any ideas?

=IF(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) ))))