IF without ELSE

A

avian2

Is it possible to use the IF function without an else clause?
I have a cells that automatically update for an external source, I'd
like to validate that the value is a number before passing it on via
DDE.

I.e.

A1 = 100
B1 = IF(ISNUMBER(A1),A1,do nothing)
 
A

avian2

Thanks for you help Don. I did try what you suggested - the issue is
that the numbers are pricing information and the consumer interprets ""
as 0 and publishes an asking price of $0. I tried writing a function

Function ValidateRT(SourceVal As String, DefaultCell As String)
If IsNumeric(SourceVal) Then
Range(DefaultCell).Value = SourceVal
ValidateRT = SourceVal
Else
ValidateRT = Range(DefaultCell).Value
End If
End Function

to use an intermediate cell for swapping value but it looks like you
can't change the value outside of the cell that the function is invoked
from. This works as a subroutine but then I don't know when to invoke
it. Is there a trigger that can be used to call a sub when the sheet
has finished calculating?

Cheers Ave
 
M

Michael76

What exactly do you want Excel to do?

If A1=100, then ISNUMBER(A1) will resolve as TRUE. You're saying that
you want to configure your IF statement so that B1=100. That part I
understand.

If A2=n/a, then ISNUMBER(A2) will resolve as FALSE. What do you want
to have happen if the value in column A is not a number?
 
H

Harlan Grove

that the numbers are pricing information and the consumer interprets
"" as 0 and publishes an asking price of $0. I tried writing a
function

Function ValidateRT(SourceVal As String, DefaultCell As String)
If IsNumeric(SourceVal) Then
Range(DefaultCell).Value = SourceVal
ValidateRT = SourceVal
Else
ValidateRT = Range(DefaultCell).Value
End If
End Function

to use an intermediate cell for swapping value but it looks like you
can't change the value outside of the cell that the function is
invoked from. . . .
Click to expand...

Correct. VBA functions called from cell formulas can't do anything formulas
normally can't do, and changing other cells is something formulas can't do.
. . . This works as a subroutine but then I don't know when
to invoke it. Is there a trigger that can be used to call a sub when
the sheet has finished calculating?
Click to expand...
....

Yes. There are Calculate and SheetCalculate event handlers. They can store
previous values of specific cells, compare current to saved values, and take
appropriate action when values change.
 
A

ave

The number in A1 is a price quote provided by a 3rd party that needs to
be validated and passed on to another 3rd party add in.
In reality it's a series of known prices that are used as the unlying
points for generating a curve which can be used to price some other
items; but in essence the problem comes down to, if the price in one
cell is valid then copy it to another cell, otherwise don't.

E.g. if A1=99.99 then it's value should be copied to B1
if A1="NAN" then the value in B1 should remain unchanged.

Thanks for your help.
 
M

Michael76

Are saying that there may be prices in both column A in column B? Is
your logic that if there is a number in column A, then replace the
price in column B with the price in column A? If there is no price
(i.e. no number in column A), use the price in column B? Then why
don't you use column C as the price you pass along to your 3rd party?

C1:=IF(ISNUMBER(A1),A1,B1)
 
A

ave

That doesn't work because we need the latest valid price - not just a
static price.
One external process is updating A1 and a second is reading from B1.
The second process could be reading the value directly from A1 but we
want a level of indirection to validate A1.
 
M

Michael76

I'm still not clear on what you're trying to accomplish. Could you
post a few lines of sample data?

Column A is updated through an external process and shows the latest
price. Is that correct? A second external process is reading values
from column B. Is that correct?

Are you saying that when the first external process runs, it could
enter non-numeric values in column A? If that's the case, can your
external process do the validation? Can't it be written to update only
if the price is numeric? If the process can't do this, why wouldn't
the IF statement work?

Your Excel file would be set up as follows:

Column A = Most recent price, updated by external process.
Column B = Last valid price. (You might have to copy values from
column A to this column prior to running the update. This could be the
tricky part depending on the size of your file and the amount of data.
How do you plan on handling data points that do not have prices during
your analysis?)
Column C = Validation.

Good Luck.
 
A

ave

Thanks for all of your help, What I need to do is iterate over the top
table copying the values into the bottom table, where and only where
the value in the top table is a number. Eg. the 3Y bid price for the
treasury value is not available - in this case we'd want to use the
prior quote. The prices are generally only unavailable for a matter of
seconds - the main problem has been, as you suggested, storing the last
valid price.

Treasury Prices Swap Spread
Bid Price Ask Price Mid Yield Bid Price Ask Price
2Y 99.28+ 99.286 4.055 35.0 39.00
3Y NA 100.056 4.062 38.2 42.25
5Y 100.072 100.07+ 4.073 41.0 45.00
10Y 100.200 NA 4.171 42.0 46.00
30Y 115.140 115.150 4.364 43.0 47.00

Treasury Prices Swap Spread
Bid Price Ask Price Mid Yield Bid Price Ask Price
2Y 99.28
3Y 100.052
5Y 100.072
10Y 100.2
30Y 115.14

As Harlan hinted at in a previous post I've been able to use a
subroutine that is called when the spread sheet recalculates to do
this.

Private Sub Worksheet_Calculate()
For i = 3 To 8
Dim sourceCell As String
sourceCell = "B" & Trim(Str(i))
If IsNumeric(Range(sourceCell).Value) Then
destCell = "B"
destCell = destCell & Trim(Str(i + 9))
Range(destCell).Value = Str(Range(sourceCell).Value)
End If
Next
End Sub

I'm sure it's not the most elegant solution and hardcoding the cell
range is ugly but it looks like it might work.

Cheers Ave
 
M

Michael76

How often do you update the cells in the top table? Do the cells
change every few seconds, or do the prices themselves change every few
seconds?

Is it possible that once you have a price that is can later be "NA"?
For example, can you have this:

3Y
100.5
100.2
NA
100.25
NA
100.4

Depending on how often you need to poll your external data, it may be
easier for you to store the raw data somewhere and add some kind of
time stamp along with the data. Then you can examine your most recent
datapoint to see whether it's numeric or not.
 
A

ave

Michael - thanks very much for your help. We don't actually poll the
pricing data - the pricing source pushes it to us. The prices are
typically sent every couple of seconds but not necessarily all at the
same time, so A3 might update five times in the space of A5 updating.
Occasionally there'll be a break in the feed for several minutes and
this is typically when we see all the prices become 'NA' or some
error string. Even in these cases we need to continue to publish
prices; if this happens we want to just use the latest available market
data sent to us. Unfortunately the pricing source and consumer add ins
do not allow for any customization so anything we need to do the price
between receiving it and passing on can only be done in excel unless we
write our own add in - which we may have to....
 
T

Tushar Mehta

I would use the worksheet *change* event.

It tells you which cell(s) changed (so you don't have to hard code as
much).

It is triggered by an external data feed. (the calculate event may not
be).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top