I'm looking to generate quite a complicated school project and i'm not sure if the formula i want to create is possible. I need whatever number is in the account column to generate an income figure. i want to be able to change the account figure to show the effect on growth. If for example cell D6 is 10,000 then i need that taken from band 1 which is 10% growth so the income would be 10,000*10% whereas if cell D6 was 22,000 then that is in band 3 which is 20% growth so therefore i would want the 22,000*20% to show the income.
If its possible i know what i mean but i can't implement it.
If D6 is >or= to 0 (I5) but <or=to 15,000 (I6) then D6 should be multiplied by 10% (H5)
If D6 is >or= to 15,000 (I6) but <or= to 20,000 (I7) then D6 should be multiplied by 15% (H6)
If D6 is >or= to 20,000 (I7) but <or= to 25,000 (I8) then D6 should be multiplied by 20% (H7)
If D6 is >or= to 25,000 (I8) but <or= to 30,000 (I9) then D6 should be multiplied by 25% (H8)
If D6 is >or= to 30,000 (I9) but <or= to 50,000 (I10) then D6 should be multiplied by 30% (H9)
If D6 is >or= to 50,000 (I10) but <or= to 60,000 (I11) then D6 should be multiplied by 35% (H10)
If D6 is >or= to 60,000 then D6 should be multiplied by 40,000 (H11)
Where D6 is used i would need to implement the formula for cell range D6:D12
Many Thanks
Hi Theo,
I'm not sure if I understand your question correct, because of two things in the file.
First I don't understand column C, or is that just an example for something else?
Second I don't understand column J. Because the numbers there don't seem to follow the ones in column I correctly.
If you just want to magnify with a certain percentage depending on the amount in the cell D6, as described in your text then you can use a Vlookup but you have to switch columns H and I for that. I'll come back with an example, although not sure if this is what you want.
Here an example.
It's not working for values below 15.000, but don't know if you need that? Otherwise the table must be adjusted.
Hi Theo,
A simple index-match combination should work:
=D6*(1+INDEX({0.1,0.15,0.2,0.25,0.3,0.35,0.4},MATCH(D6,{0,15000,20000,25000,30000,50000,60000},1)))
In this formula, the constants are hard typed into the formula, but you can place them in a lookup table if you want. The table needs to be sorted smallest to largest, and the formula will be referencing that table:
=D6*(1+INDEX(LookupTable[Percent],MATCH(D6,LookupTable[Value],1)))
