Creating pop up alert in the cell containing formula

QuestionEdit

I have a cell which has a formula. I need to validate the formula result such that if a user inputs a number in the formula source cell and if the formula result is 10% more or less than the average of previous two cells a message box appears saying that it is an incorrect entry.

Eg : The sheet is for financial projections and The rows 12 and row 13 are inputs and entered periodically (they can be named as interest eanred for Row 12 and interest expenses for row 13 and both these are in column E. The columns contain the year as in yr 2009, 2010 etc. and row 14 (which is named net interest income) is computed from row 12 and row 13. i enter the data for row 16 (which is interest bearing assets) and automatically row 19 is computed as row 14/row 16. Now i want a pop-up alert when row 19 (in column E) exceeds 50% or is lesser by 50% of the average of D19 and C19 (here these two cells are also computed as explained above). Subsequently, when i enter data for other columns, i need to get a pop-up alert when F19 exceeds 50% of the average of E19 and D19.

I tried doing data validation as follows : =OR(E19>(AVERAGE(C19:D19)+50%),E19<(AVERAGE(C19:D19)-50%) However it does not work and i am trying to find out any macro for the same.

I have also uploaded the file as follows :

Download link : http://wikisend.com/download/967276/FOR UPLOADING.xls

forum link : [URL=http://wikisend.com/download/967276/FOR UPLOADING.xls]FOR UPLOADING.xls/URL

Thanks &

AnswerEdit

treeline

This is the way I usually handle your situation--

Calculate your acceptable criteria in a cell separate fromt he one that you are using the data validation in and then base the data validation on the results of that cell.

For example--

Assume you are validating the cell A10 and you want A10 to not be greater than 110% the average of cells A1 thru A9. So your data validation pop up needs to occur if an attempt is made to put in a value greater than 110% of the average of cells A1 thru A9.

If you put a formula anywhere on the sheet and then refer to that in your data validation it is much simpler to troubleshoot and to create the formula.

So lets say in cell Z50 I put the formula

=IF(A10>AVERAGE(A1:A9)*1.1,"yes","no") the result in Z50 is either yes or no and if A10 is in excess of 110% of the average of A1 thru A9 a yes will be produced in Z50.

Now I can put in my data validation formula =Z50="no" because a no means that the number is allowable.

Obviously you need to adapt this to your criteria and your data but the method works much beter than trying to include all of the criteria in the validation formula itself.