A Better Way to Show an Error in the Calculation??

[Susan J]

I have highlighted the two columns in question in pink. In the first, the Total ACV & Replacement Cost quantities cannot exceed the Total Item Qty. The second, is the a manual entry in the Replacement Cost cell cannot be greater than the Estimated Replacement Cost. I appreciate any feedback.


http://www.2shared.com/file/2bwiSgPQ/CVR-Test_2012_12_08.html

 

[NARAYANK991]

Hi Susan ,


The comment I made against an earlier question of yours , applies here too.


When specifying a problem area , it would be much better to give precise references to the worksheet , such as cell E33 , column C , and so on , instead of mentioning "two columns highlighted in pink" ; I have downloaded your file , and nowhere can I find two columns highlighted in pink. This may be due to a problem in my PC , monitor , my configuration or any other reason ; if you specify column C and column D , there can be no source of confusion.


Narayan

 

[Susan J]

You are absolutely correct and I apologize. I failed to unhide Columns P & T that my question refers to. If you do so you will see what my question refers to. Someone mentioned that perhaps the value refered to in Column T could present in red font instead of a TRUE or FALSE statement.


I apologize again and will be painfully clear in the future.

 

[NARAYANK991]

Hi Susan ,


I went through your formulae in the various cells , and I am not able to understand your exact requirement. Let me put down the formulae here :


1. N20 has the formula : =B20


This means that N20 will always have whatever value is there in cell B20 i.e. N20 and B20 will never differ.


2. R20 has the formula : =IF(N20=B20,0,B20)


This means that if N20 and B20 are equal , R20 will have the value 0 , otherwise , it will have the value B20.


However , since N20 and B20 will always match because of the formula in (1) , R20 will always be 0.


3. P20 has the formula : =(N20+R20)>B20


This means that P20 will be TRUE if (N20 + R20) is greater than B20 ; otherwise it will be FALSE.


However , we have seen in (1) that N20 = B20 always ; in (2) we have seen that R20 = 0 always ; therefore (N20 + R20) will equal N20 , and since N20 is always equal to B20 , it can never be greater than B20.


Hence , by the formulae which have been used , P20 will always be FALSE. It can never become TRUE.


Can you specify what exactly you want to check ?


Narayan

 

[Susan J]

Hi Narayan


I am hoping the following explanations will help:


1) I had to build this sheet to default first to Actual Cash Value Bulk Total and then to Replacement Cost Bulk Total and then drill down to the respective Per Item Totals.


I also built this sheet for manual data entry to B20 to H20 & J20 and the rest self-calculates. N20, R20 & S20 may need to be manually changed later and I shall explain why this is as I continue.


So, I want N20 to default to the entry in B20 because this is how the data is first entered so, if N20=B20, R20 must be O.


As the adjusting process continues, however, R20 may need to be manually changed to equal B20 and N20 may have to be manually changed to 0. Conversely, if B20 is 6, for example, N20 may need to be manually changed to 3 and R20 may need to be manually changed to 3(or some other variation of X+Y=6. In either case, the total of N20+R20 cannot be more than B20. That is the reason for the formula in P20, to determine if N20+R20 in not > or < B20.


I appreciate that these are pretty simple formula's but I believe they work. I know I could attain what I am currently using in P20 in Conditional Formatting but, I do not know how to set that up in the actual cells N20 & R20.


I am also guessing there are formulas to insert that will cover all of the above scenarios and that is where any ideas you have to make the input of this data more "idiot proof" are appreciated.


2) Following a similar rationale to the above, S20 can never be greater than J20 which is what the formula in T20 addresses. (Actually I used K20 in my formula which is actually incorrect, it should be K20)


Is all of this making sense? I hope so.

 

[NARAYANK991]

Hi Susan ,


Things are clear now ; however , my doubts are over the formulae in N20 and R20 , since once you make a manual entry in either of them , the formulae will be overwritten.


The formula in P20 is perfectly valid , and will serve as a check for the figures that you mention.


I don't know of any simpler way of tallying the columns of interest.


Narayan

 

[Hui]

Have you had a look at: http://chandoo.org/wp/2010/10/28/hiding-error-messages-quick-tip/

 

[Susan J]

Thanks Hui

How did you get to know all of this information and it seems to be innate to you! I am majorly jealous.


Have you looked at the file in my "Creating Custom Screen with Userform" question. It has formula's in it i.e. E9-19 but, you cannot see them. Why is that? It makes for a clean looking form not like the one I created where the input rows show a 0 until the required data is populated.


Sure would appreciate your expertise in learning why the above and how to create the UserForm and Macro to automatically add lines. You will need to be specific and walk me through it because my brain is not yet as intuitive as yours.


Let me know either way.

 

[Hui]

Susan


I've been using Spreadsheets since VisiCalc and so I suppose it is just stuck in the grey matter


In regards E9:E19 the cells have a display format set to: $#,##0.00

so that when there is no values in Column J or M it won't display anything

Put a value in J19 and see


You can change the display format by Select the cells Ctrl 1, Number, Custom Number Format

select an existing code or enter your own eg: $#,##0.00;-$#,##0.00;$0

 

[Susan J]

Thanks Hui


Yes, I do see what you are saying. I have deduced that this may be due to the way this file is protected also because both Locked and Hidden are activated. Does that make sense?