[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-12-22
2
Medium Priority
?
274 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 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 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