Solved

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

Posted on 2013-12-22
2
268 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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