macro to calculate or make/update formula

[jat]

i have the following in cell D2:
"=IF(C2<C4,D4,IF(C2<C5,D5,IF(C2<C6,D6,IF(C2<C7,D7,IF(C2<C8,D8,D9)))))"

basically, in C2 is a value; column B from row 4 down has the lower bracket
and column C from row 4 down has the upper bracket; Column D will have
another value that will calculate back into D2. ex:

MIN MAX RESULT
0 100 28
101 200 25
201 300 22
301 18

and so forth..., if C2 has a value of 223, D2 would show 22. the problem is
sometimes the min and max values will change and the amount of brackets will
also increase or decrease depending on different years, etc.

i need a macro that will autopopulate cell D2 - if possible;

thanx

 

[Bob Bridges]

A program to do that is easy, but actually I think you can do it with the
VLOOKUP function. Let me look it up for a second....No, VLOOKUP is willing
only to take the next smaller value, whereas you want the next larger. For
that you have to use MATCH:

=MATCH(C2,C4:C99,-1)

With -1 as the third argument, MATCH needs C4:C99 to be in DESCENDING order
and it will find the value that is equal to or just greater than C2. If the
values in C4:C99 are 500, 400, 300, 200 and 100, then MATCH will find the 300
value, which will be in C6; that's the third row of the table so it returns
3. You turn than into the address D6 by using the INDEX function, like this:

=INDEX(C499,MATCH(C2,C4:C99,-1),2)

That way if MATCH returns 3, then INDEX looks at row 3 col 2 of C499,
which is just the value you want. Forget the long complex IFs, and forget
VBA programming too; this should be simpler.

If for some reason you still want to do a VBA program, it's still pretty
easy. But I always prefer formulae and functions when possible, and I
imagine you do too.