Solved

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

Posted on 2013-12-22
2
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Outlook 2016 10 44
Change conditional formatting highlight specific reference 13 30
WORKDAY formula question 4 11
Excel Formula 6 25
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 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