Look up using percentages

[dodger999]

Guys need a look up table here i think hopefully somebody can help
i have a figure of 395.00 euros to start and need to calculat percentages when i enter a certain figure
so if i enter a figure between 0-5 i get 90% of 395 to reclaim back
6-10 i get 70 % of 395
10-15 40 %
15-20 20 0%
20 above 0%

sorry never been good with working out percentages

cheers if you can help

[darkyam]

What happens if you get 10? According to your table, you get either 70% or 40% back.
Assuming you meant 11-15 (and so on), use =LOOKUP(B1,{0;6;11;16},{.9;.7;.4;0})*A1, where A1 has your 395 and B1 has your other figure.

[dodger999]

sorry 11 - 15 and 16-20

[darkyam]

Edit to previous formula (didn't see the 20%, misread as 0% with the space)
=LOOKUP(B1,{0;6;11;16;21},{.9;.7;.4;.2;0})*A1

[dodger999]

that was a great help thank you very much

[dodger999]

sorry guys i know ive entered solved but how did you get to do 2 arrays in the formula when i try it goes at the beginning of the argument

thanks

[darkyam]

I don't quite understand. I just typed them in. If you're outside the US, you might have to switch ; with , in the formula. If you still have problems, could you please post a sample workbook?

[dodger999]

Ok i got you i thought they were done using CSE method no problems here no thanks once again for your time and patience im still learning

[darkyam]

No problem. The CSE method is to make a normal formula an array formula, which is different than manually entering an array. If you have a function that normally applies to one cell at a time and you want to apply it to a range, such as =Day(A1:A10)>5, that's when CSE comes into play. Note, though, that most array formulas can be taken care of with Sumproduct or, in the case of Excel 2007, perhaps SUMIFS, COUNTIFS, or AVERAGEIFS.