Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will FLOOR & CEILING work with variable stratifications?
I am attempting to use a lookup formula to determine the next iteration in an
array. For instance, if the cell = 21,000 then I need the result to be the value in the array immediately lower than 21,000, in this case 20,000. In the next cell, I need the result to be the value in the array immediately greater than 21,000, in this case 25,000. The FLOOR and CEILING formulas would normally work but, the stratifications are not static. The values in this array change from 1,000 to 2,000 to 5,000 to 10,000 in different places in the array. The LOOKUP function seems to work for the value immediately less than the cell value. Unfortunately, for the greater than value, I am currently using nested IF statements like =IF(C7<10000,H7+1000,IF(C7<=20000,H7+2000,IF(C7<=5 0000,H7+5000,IF(C750000,H7+10000)))) This seems to work but there has to be a cleaner way that is more idiot proof. Does anyone have any ideas? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will FLOOR & CEILING work with variable stratifications?
Try this formula
=H7+LOOKUP(C7,{1,10000,20001,50001},{1000,2000,500 0,10000}) With best wishes Sreedhar "johnu" wrote: I am attempting to use a lookup formula to determine the next iteration in an array. For instance, if the cell = 21,000 then I need the result to be the value in the array immediately lower than 21,000, in this case 20,000. In the next cell, I need the result to be the value in the array immediately greater than 21,000, in this case 25,000. The FLOOR and CEILING formulas would normally work but, the stratifications are not static. The values in this array change from 1,000 to 2,000 to 5,000 to 10,000 in different places in the array. The LOOKUP function seems to work for the value immediately less than the cell value. Unfortunately, for the greater than value, I am currently using nested IF statements like =IF(C7<10000,H7+1000,IF(C7<=20000,H7+2000,IF(C7<=5 0000,H7+5000,IF(C750000,H7+10000)))) This seems to work but there has to be a cleaner way that is more idiot proof. Does anyone have any ideas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't get two variable tables to work | Excel Worksheet Functions | |||
can't get two variable tables to work | Excel Worksheet Functions | |||
Something like CEILING or FLOOR | Excel Worksheet Functions | |||
ceiling & floor | New Users to Excel | |||
work roster with variable inputs | Excel Discussion (Misc queries) |