Solved

Want the positive value, ignore 0, negative, N/A ect

Posted on 2013-12-22
2
267 Views
Last Modified: 2013-12-23
Hi All,

I have some data that updates every few minutes or so with no set schedule. The data could also update 50 times, or more, in one minute. Basically, I am getting the data from an external source so I have no control over it.

When the data is updating it can become "" or negative or N/A or anything else.  All I am interested in are positive values and want to ignore all the rest until the data updates again. As soon as the data updates again I want to capture it.

Therefore I would like to come up with a work around.

Here is what I am imagining.

Premise:
Suppose that I have a positive number in excel worksheet1 S4.  

I want to have a way to do the following:

1. As soon as the positive number in the Premise changes (up or down) I would like to copy S4 and paste it in to T4.

2. If the data is updating and S4 becomes zero, negative, nothing, "", or N/A or (anything but a positive number) I would like T4 to remain the same as it was before.  Once S4 is positive again then S4 would be copied into T4 immediately.

Thanks for your help!
0
Comment
Question by:BostonBob
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39735399
If you turn iterations on, you should be able to put the following formula in cell T4:
=IF(ISNUMBER(S4),IF(S4>0,S4,T4),T4)

To turn iterations on in Excel 2010 or later:
1.  Open the File...Options menu item
2.  In the Formulas tab, check the box for "Enable iterative calculation"

I am concerned, however, that what you have requested is not necessarily what you need to solve the real problem. You may need a worksheet event macro, for example, which can perform a series of calculations based on the updated value in cell S4.
0
 

Author Comment

by:BostonBob
ID: 39735994
It is exactly what I need.  It works. Thanks so much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now