[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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

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
BostonBob
Asked:
BostonBob
1 Solution
 
byundtCommented:
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
 
BostonBobAuthor Commented:
It is exactly what I need.  It works. Thanks so much.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now