I currently have the following formula that references the data in cell BJ13 and shows what the next number range will be depending on the number range entered in BJ13:
My range of numbers needs to change now and I can't seem to figure out how to use a similar formula to properly calculate the results based on the new range of numbers. Can I get some help? Here is the chart I'm basing this off of:
Code:
=IF(OR(BJ13>=435000,BJ13=0),"",IF(BJ13<1000,"2nd lvl (1000)",MATCH(BJ13,INDEX(ZZ*(ZZ+1)*500,0))+2&LOOKUP(MATCH(BJ13,INDEX(ZZ*(ZZ+1)*500,0)),{1,2,19,20,21,22;"rd","th","st","nd","rd","th"})&" lvl ("&LOOKUP(BJ13,ZZ*(1+ZZ)*500,(ZZ+1)*(ZZ+2)*500)&")"))
My range of numbers needs to change now and I can't seem to figure out how to use a similar formula to properly calculate the results based on the new range of numbers. Can I get some help? Here is the chart I'm basing this off of:
Code:
[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]1st[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]4th[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD]5th[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]6th[/TD]
[TD="align: right"]23000[/TD]
[/TR]
[TR]
[TD]7th[/TD]
[TD="align: right"]35000[/TD]
[/TR]
[TR]
[TD]8th[/TD]
[TD="align: right"]51000[/TD]
[/TR]
[TR]
[TD]9th[/TD]
[TD="align: right"]75000[/TD]
[/TR]
[TR]
[TD]10th[/TD]
[TD="align: right"]105000[/TD]
[/TR]
[TR]
[TD]11th[/TD]
[TD="align: right"]155000[/TD]
[/TR]
[TR]
[TD]12th[/TD]
[TD="align: right"]220000[/TD]
[/TR]
[TR]
[TD]13th[/TD]
[TD="align: right"]315000[/TD]
[/TR]
[TR]
[TD]14th[/TD]
[TD="align: right"]445000[/TD]
[/TR]
[TR]
[TD]15th[/TD]
[TD="align: right"]635000[/TD]
[/TR]
[TR]
[TD]16th[/TD]
[TD="align: right"]890000[/TD]
[/TR]
[TR]
[TD]17th[/TD]
[TD="align: right"]1300000[/TD]
[/TR]
[TR]
[TD]18th[/TD]
[TD="align: right"]1800000[/TD]
[/TR]
[TR]
[TD]19th[/TD]
[TD="align: right"]2550000[/TD]
[/TR]
[TR]
[TD]20th[/TD]
[TD="align: right"]3600000[/TD]
[/TR]
[TR]
[TD]21st[/TD]
[TD="align: right"]2100000[/TD]
[/TR]
[TR]
[TD]22nd[/TD]
[TD="align: right"]4200000[/TD]
[/TR]
[TR]
[TD]23rd[/TD]
[TD="align: right"]8400000[/TD]
[/TR]
[TR]
[TD]24th[/TD]
[TD="align: right"]16800000[/TD]
[/TR]
[TR]
[TD]25th[/TD]
[TD="align: right"]33600000[/TD]
[/TR]
[TR]
[TD]26th[/TD]
[TD="align: right"]67200000[/TD]
[/TR]
[TR]
[TD]27th[/TD]
[TD="align: right"]134400000[/TD]
[/TR]
[TR]
[TD]28th[/TD]
[TD="align: right"]268800000[/TD]
[/TR]
[TR]
[TD]29th[/TD]
[TD="align: right"]537600000[/TD]
[/TR]
[TR]
[TD]30th[/TD]
[TD="align: right"]1075200000[/TD]
[/TR]
</tbody>[/TABLE]