I need to calculate the following using circular reference
Value Criteria1 Criteria2
$50.00 15 15
$30.00 40 200
$80.00 80 90
$100.00 120 30
If Criteria 1 or Criteria 2 is less than 100, leave the value inputted, otherwise you should have 0. For instance, $100 should turn into 0 since criteria 1 has 120.
Any ideas?
How does the number in the value column get there? Is it pulled from elsewhere or is it manually entered? If it is pulled from some other cell(s), you could just use an IF formula in the value column. Something like:
=IF(MAX(B1,C1)>100,0,FormulaThatPullsValue)
If it's manually entered and you need it to drop to zero if a criteria exceeds 100, that could be achieved using a VBA change event. It would be helpful to see a representative sample workbook (use: Go Advanced --> Manage Attachments to access the upload window). Be sure to alter or remove any sensitive data.
Should $30 drop to 0 as well, since criteria2 is 100?
If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".
If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
https://www.excelforum.com/the-water...ke-thread.html
It has to be easier way. Would it work with data validation? What formula could I put in there? Yes, $30 should drop to 0. Value is manually entered and it is not a formula.
Last edited by olga6542; 11-16-2017 at 01:18 PM.
It's hard to say without seeing anything. Are you able to post a sample workbook?
Here is the attachment
Unfortunately, there is not. You cannot have both a formula and a manually entered value in the same cell. The formula is required to test the logic.Originally Posted by olga6542
As CAntosh stated in post #2, this can be done, but it will have to be through VBA.
Do you want a value AND a formula in the one cell??? If so, you can't.
This formual will work inD2, copied down, but NOT in A2...
=IF(OR(B2>=100, C2>=100),0,A2)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Assuming the data in A is manually entered, you can use a helper column (see Glenn's approach) or VBA:
The procedure above should change the A value to zero if a criteria value over/equal to 100 is entered.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks