Show ERROR if result is not within 0.05% of any of 8 percentage points

Hello All

Hope someone can help.

Column O has a formula which calculates the percentage of 2 other cells, this percentage must be with 0.05% of any of the below percentages which are in a separate sheet

4.29%
5.65%
5.91%
7.68%
8.81%
9.72%
11.72%
13.51%

If the result is within 0.05% either way of ANY of those figures then OK if not then ERROR

Thank you for any help

Thank you for the reply

Ive never used lookup tables so not sure but I will try

The other sheet which is only for reference simply shows monthly options and % cost of credit
Months %interest
6 4.29%
10 5.65%
12 5.91%
18 7.68%
20 8.81%
24 9.72%
30 11.72%
36 13.51%

Staff manually enter the loan value in column J and manually enter the cost to us in Column L

Column J Column K Column L Column M
Loan Amount Monthly Payments "IFC Subsidy Cost" Percentage Subsidy
£5,000 £417 £500.00 10.00%

Column M calculates L/J as a percentage
We simply need to check that this figure is within the parameters 0.05% depending on the monthly term. This should expose any inputting errors

I will look into the tutorial you suggested


Thanks

Dear POncho147

try this formula

Formula:

Please Login or Register  to view this content.

my understands my knowledge i try this formula. you get any error or you get any wrongs formula please attached the sample workbook

THanks for the reply but im not sure how this would work as I need to check if the figure in column M is within 0.05% of any of the figures in the %interest column

Please attach a sample workbook.

Copy of IFC TEST.xlsm

Column O shows the subsidy which cannot be more than 0.05% away from any of the percentage points in table in IFC Terms sheet

Column R needs to show "ERROR" or a message of my choice if this figure is more than 0.05% either way
ALl greyed out columns will be locked, others are manually entered

Percentage rates change according to term which is in column I and a dropdown of the 8 choices



Hope that helps

Thank you for any help

Try, =IF(ABS(O15-INDEX(LookupTable[Interest],MATCH(I15,LookupTable[IFC Terms],0)))<=0.05%,"OK","ERROR")

Thank you so much for your replies and time to help MrShorty but as I havent used lookup tables before, I tried josephteh's suggestion above and it works perfectly.


Thanks you to all

THis is fantastic josephteh


Thank you very much

HI joseephteh

Could I ask you to help with a little alteration of this task please?

INstead of allowing users to input the subsidy cost in column L
Would it be easier/possible to have this figure auto calculated from the term in column I , the lookup table of interest rates and the loan amount in column J

This would require
Column O to use the term in the lookup table to show the correct percentage
Column L to calculate the subsidy by using the % rate shown in Column O and the loan amount in column J

I hope thats clear :/



Thanks again for any help

I have no idea how you calculate the subsidy cost.

You have not answered my question: what is the formula for the subsidy cost? Can you give an example of what you have got?

The subsidy cost is entered manually from the statement the lenders send to us and is based on the interest rate they charge us depending on the term of the loan

Say the loan value £1000
Terms taken from the lookup table

6 months = 4.29%
10 months = 5.65%
12 months = 5.91%
18 months = 7.68%
20 months = 8.81%
24 months = 9.72%
30 months = 11.69%
36 months = 13.51%

subsidy cost in the case of a 12month term would be 5.91% of £1000
In the sheet COlumn J is loan value and column O is percentage subsidy (taken from lookup table above) so simply J15*O15 (£1000*5.91%)

Looking at this, I think all I need is for column O(subsidy %) to be a formula that enters the correct % from the lookup table depending on the Term chosen in Column I which is partly what you did last time.
That way, I could then just have subsidy cost using the simple formula above J15*O15


Thanks again

The formula for Percentage Subsidy is: =INDEX(LookupTable[Interest],MATCH(I15,LookupTable[IFC Terms],0)).

How did you get £300 Subsidy Cost in your example?

THanks again for your help but I think ive found the answer


=VLOOKUP(I15, 'IFC Terms'!B:C, 2, FALSE)

This appears to work, would you agree this is correct based on the above and the sheet I attached



Thanks

Yes, VLOOKUP can be used instead of INDEX+MATCH. You have to watch out though that no columns should be added between columns B and C, otherwise VLOOKUP will fail.

Thanks again

I will also try your way, hopefully learn a bit

The £300 figure was just a manually entered one which I used to test the other formulas, sorry if it confused things


Cheers