IF function 1

[Mark2Aus]

I was just wondering if there is a way in the IF function to return a value if logic is true and not to change that value any longer.
Thus, I receive online in cell A1, 20 different prices every day and I would like to have all them recorded starting from cell B1 to B20.
When a write in every one of the cells, from B1 to B20 the same formula:
IF(ISNUMBER($A$1),$A$1,""), of course as A1 changes during the day, all of my B1 to B20 will change and show the same value as the last value in A1.
Is there a way to, after the first value is shown in A1, appearing also in B1, that it will not change any longer in B1 and only, when A1 changes again, B2 will then record that value, but not B1. And so on...
Any ideas?
Mark

 

[SkipVought]

Hi,

Not with formulas. Takes VBA code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Dear Mark,

I really don't think you should use the solution given below; skip is right, VBA is the right way forward. But I can't resist a challenge.

Go to tools, options, calculations, and turn on iteration. We are going to need circular references.

Now enter in B1
=if(B1<>0, B1, A1)
Enter in B2
=if(B2<>0, B2, if(and($A$1<>0, B1<>0, $A$1<>B1), $A$1, 0))
And copy this down as far as you want.
Before going any further, copy the whole of column B over to another column. This is important (as is the lack of $ in the reference to A1 in B1) because the entering of values is about to become irrevocable. If you don't do it, you have to type the whole thing in again to restart the process.

Now you have a list of zeroes in column B.
Each time you enter a non-zero number in A1, it will appear in column B, and subsequent non-zero numbers will appear in successive rows of column B.

 

[kjv1611]

Mark2Aus,

Do let us all know if anything works. This is an interesting one, challenging for sure.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Another caveat with a spreadsheet solution is that values of ZERO are never captured, if that makes any difference

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

Hi guys,
The spreadsheet solution (lionelhill's) works just fine. I've just tested it and every time a new number apppears in A1, a new cell shows a permanent value down column B. Brilliant solution!!!

I would like to confirm with you lionelhill, why the need for copying the whole of column B over to another column? Is it to, at the end of the day, copy back over column B as column B now has values in it (it became irrevocable) and no more the formulas? And this process will be repeated everyday.

And, Skip, your observation is spot on, as this will not record any zeros. But I've checked and it is OK. No zeros will ever happen.

I will tomorrow test the VBA solution and I will let you know the results.
Thanks everybody for the great help.
Mark

 

[Mark2Aus]

Hi everyone,
Skip's VBA solution works just fine. Excellent. And with a contribution by PHV(MIS), no more repeated numbers as well.

But, to make things more challenging, as I expect the numbers of different numbers appearing on A1 to increase substantially, besides not recording repeated numbers anymore, I would like to record only numbers which shows turning points in the sequence, that is to record only numbers that represent a change in the direction the numbers were going. To be regarded a turning point, the change must be by at least 5 points.

Example: To define a direction at least 2 numbers must be recorded in B1 and in B2. Thus, if A1 shows 60, 60 is recorded in B1, then where A1 shows 62, 62 is recorded in B2 (indicating that the direction is Up), but when A1 shows again 61, nothing is recorded (it is agaisnt the direction but by only 1 point). But, if A1 now shows 63, 63 is then recorded in B2 (not in B3, as 63 is not yet a turning point). If then A1 shows 64, the same 64 is recorded still in B2 (as 64 is not yet a turning point) but if then A1 shows 59 (5 points against the direction the numbers were showing, which was to move higher) then 59 is recorded in B3, leaving 64 irrevocably recorded in B2, as 64 was a turning point. And so on.
Is this possible to do without writing a code?
Thanks
Mark

 

[lionelhill]

Yes, the reason for copying the formula-based solution over to another column was so that you can copy back to reset the process. Otherwise I don't know of a way to blank out the values of a cell without deleting the formula.

There is another issue besides the very real problem of not recording zeroes: the spreadsheet version will also not record an unchanged value. So if you receive price information 20 times a day, but sometimes the price doesn't change, you won't record the "new" identical price.

I'm glad the VBA solution worked; I think it's much more elegant to go that way.

 

[Mark2Aus]

Thanks lionelhill for the great help.
Is there a way in your formula solution not to populate the whole range with zeros, but instead with blanks? The reason for the question is that as I am going to plot the range with data as they occur, having zeros all the way down the range will make my chart impossible to read as the chart will link the prices, say 4300, 4320, 4344, etc,. with zero and the chart will be useless.
Is it possible that the chart either will update automatically only with the values as they are received? or could the formulas be changed to reflect this? I've tried changing your formulas to:
in B2: if(B2<>0,B2,if(and($A$1<>0,B1<>0,B1<>"",$A$1<>B1),$A$1,"")) but it does not work.
Thanks for any help

 

[lionelhill]

I haven't thought about this a lot (and I still prefer the idea of VBA) but being a multi-column sort of person, I'd be inclined to put in C1 something like
=if(B1=0, "", B1), and copy that down the column. Then I'd hide column B. Hope that helps.

 

[GSCaupling]

Mark2Aus,

Could this just be a matter of custom formatting?

Try this: _(* #,##0.00_);_(* (#,##0.00);_([blue]* ""??[/blue]_);_(@_)

This displays blanks for zero values. You could also substitute _([blue]* "-"??[/blue]_) to have a hyphen represent zero values.

HTH,
GS

[small][navy]*****************^*****************[/navy][/small]
[red]I think of the word processor as the pen's e-quill.[/red]