macro to calculate or make/update formula
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 |
macro to calculate or make/update formula
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(C4:D99,MATCH(C2,C4:C99,-1),2) That way if MATCH returns 3, then INDEX looks at row 3 col 2 of C4:D99, 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. --- "jat" wrote: i have the following in cell D2: =IF(C2<C4,D4,IF(C2<C5,D5,IF(C2<C6,D6,IF(C2<C7,D7,I F(C2<C8,D8,D9))))) the problem is sometimes the min and max values will change and the amount of brackets will also increase or decrease. i need a macro that will autopopulate cell D2 - if possible; |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com