Show Error message If result not within 5% of figure in seperate table

Woodpusher147

Board Regular
Joined
Oct 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hi All

I have a sheet that calculates credit interest and need to show an error if someone inputs wrong figures.

EG
I 21 = "loan term" J21 = "loan amount" L21 = "Loan cost" O21 = "loan cost percentage"

I have a table with the loan cost percentages for each term
6m = 4.29%
10m = 5.65%
12m=5.91%
and so on until 36m

Column I (loan term) is a dropdown of the monthly choices
Column J Loan amount is a calculation based on cost less deposit (locked cell)
Column L Loan cost is manually inputted (this is the cell that could cause an error should the person input the wrong loan amount)
Column O Calculates the percentage of loan cost like so =IFERROR(L21/J21,"")

Basically, the loan cost percentage cannot be more than 5% out either way so I need column O to divide column L by column J but return an error if the % is more than 5% away from the percentage that is assigned to the on term as in my term table.

If term chosen was 10m then column O result would show error message if it was more than 5% above or below 5.65%

Hope thats clear, not sure if it is :/


Thank you very much for any help
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
WOuld really appreciate some help on this, anyone?

Or if its not possible please say
 
Upvote 0
FIrstly, the above should have been 0.05% above or below but im guessing thats not possible.

SO, is it possible and if so please help with the below

Can I have another column which will simply display "ERROR" if the result in column O is shows anything that is not with 0.05% of the following percentages

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

Thanks for any help
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top