If FALSE, leave cell blank - help please -- ("" doesn't work?)

[aiki100]

Greetings all -


I have looked high and low for this, both here and elsewhere on the net, and I am missing something I am sure that must indeed be so simple...I hope someone can help with this, thanks!




This formula below, works fine:




=IF(AND(AJ2="x",AL2="L"),J3-AR2,IF(AND(AJ2="x",AL2="S"),AR2-J3))




In the event the AND conditions are not met, the formula returns FALSE, as it should. I simply want to suppress the FALSE, and leave the cell bank so I don't have to look at it.




I have tried various versions of "", (which work every place else I've ever used them), but there is something I don't understand about what is gong on here.




Thank you so much for the help, it is very much appreciated!


Aiki

 

[AlphaFrog]

Try this...

=IF(AND(AJ2="x",AL2="L"),J3-AR2,IF(AND(AJ2="x",AL2="S"),AR2-J3, ""))

 

[aiki100]

Dear AlphaFrog


Egads! Thank you!




I knew it was something simple. Sorry to bother you, but this was very, very helpful.


I go through periods of needing to get deeper into excel, and sometimes, the syntax just escapes me - especially if I'm building longer nested things - (and yes, I'm sure that you're probably thinking to yourself, "well...that's not a very long formula..." lol...)


Anyway, much appreciated, Thank you!




Now, as long as you were kind enough to reply, may I ask for assistance with an extension of this formula as well?


I'd like to add the ability to toggle the the above formula in the following way:




1 - I have a cell AP2, called "MA Filter".


This will contain a Y or N. It is simply meant to indicate whether the filter I am trying to build should trigger or not.






2 - Cell AH2 already has a formula to indicate whether a condition, "above," or "below," exists.


So if AP2 has a "Y," a CELL needs to look at AH2 to see which of two possibilities for this condition exist, "above," or "below,"


(I suppose a third condition could exist: exactly AT, so neither above, nor below, but suspect that will not happened too often, though a way to handle that possibility would be good...)






3 - Next, I have a cell, AL2, which has a hand entered "L," or "S." This stands for Buy (L) or Sell (S).


You will notice that the formula you kindly helped with makes use of this cell, AL2, to do its thing.






4 - So if AP2 = Y, and AH2 = above, I would like AL2 to change to L.


If AP2 = Y, and AH2 = below, I would like AL2 to change to S.


This would then force the formula you helped with above to execute L (only) if AH2 = above, and S (only) if AH2 = below.





5 -If AP2= N, then nothing should happen, and I will continue to enter things in AL2 by hand...so that the overriding of manual entry into AL2 only occurs if there is a Y in AP2.







Hope that makes sense, and you are good for trying to take a crack at it.


If there is some more sensible way to proceed, I am fine with that, too. Either way, I will no doubt learn a great deal by your instruction (as I did above). That way I get better at thinking abut these things...


Thank you again for your help, and hope your day is good wherever you may find yourself.



Aiki

 

[AlphaFrog]

You're welcome.

5 -If AP2= N, then nothing should happen, and I will continue to enter things in AL2 by hand...so that the overriding of manual entry into AL2 only occurs if there is a Y in AP2.

So you want to override the manual entry in AL2 if\when there is a Y entered in AP2; Is that correct?

The only way to "override" a manual entry is with VBA code. You couldn't do it with a formula alone as a cell cannot have both a formula and a manual entry at the same time. So are you looking for a macro solution?

 

[aiki100]

Okay, understood -- I see how that would indeed be insurmountable without VBA, which as it stands is a bit above my pay grade to implement.




hmmm... I wonder if there might be a way to modify the formula above?


say:


Insert a new column AQ, called "MA Filter L/S" (or the like) that would contain (if I have this correct):


IF(AND AP= "Y", AH= "above"),"L", IF (AND AP= "Y", AH= "below"),"S" ,"")




Then...find a way to modify the formula you helped with above to accommodate the possibility of AQ containing an L or S, and if found, using the AQ (L or S) value instead to carry out the rest of the formula.


Is that sensible? Might that work?

 

[AlphaFrog]

Okay, understood -- I see how that would indeed be insurmountable without VBA, which as it stands is a bit above my pay grade to implement.




hmmm... I wonder if there might be a way to modify the formula above?


say:


Insert a new column AQ, called "MA Filter L/S" (or the like) that would contain (if I have this correct):


IF(AND AP= "Y", AH= "above"),"L", IF (AND AP= "Y", AH= "below"),"S" ,"")




Then...find a way to modify the formula you helped with above to accommodate the possibility of AQ containing an L or S, and if found, using the AQ (L or S) value instead to carry out the rest of the formula.


Is that sensible? Might that work?

I think you want your AQ formula to be...

=IF(AND AP= "Y", AH= "above"),"L", IF (AND AP= "Y", AH= "below"),"S" ,AL2)

So if it can't determine L or S, then it returns the manual entry from AL2

Then in the original formula, change the AL2 references to AQ2

=IF(AND(AJ2="x",AQ2="L"),J3-AR2,IF(AND(AJ2="x",AQ2="S"),AR2-J3, ""))

 

[aiki100]

That did it!

Outstanding -- thank you for helping me wrap my head around this: so very much appreciated.

A great solution to an interesting puzzle.

Have a super day AlphaFrog!

 

[AlphaFrog]

That did it!

Outstanding -- thank you for helping me wrap my head around this: so very much appreciated.

A great solution to an interesting puzzle.

Have a super day AlphaFrog!

You're welcome. Thanks for the feedback.

If you really want to get fancy, take your AQ formula and paste in your original formula in the two places it has AQ2.

=IF(AND(AJ2="x",IF(AND AP2= "Y", AH2= "above"),"L", IF (AND AP2= "Y", AH2= "below"),"S" ,AL2)="L"),J3-AR2,IF(AND(AJ2="x",IF(AND AP2= "Y", AH2= "above"),"L", IF (AND AP2= "Y", AH2= "below"),"S" ,AL2)="S"),AR2-J3, ""))

 

[aiki100]

AlphaFrog -

I have copied that one down for later perusal, thank you, thank you...!

Now --




Wonder if you have a thought on this (since you seem so 100% on all other things today!)


I am trying to build a formula to calculate an exponential moving average. Have searched both Mr Excel and elsewhere and have gotten several (conflicting) answers. I thought I had gotten this right yesterday, but now that I am validating, it is for sure incorrect...


Here is what I have so far, based on the research already done.




I have the following for an 8 period exponential moving average in AE11:





[FONT=&quot]=J11*2/(8+1)+AE10*(1-2/8+1)[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]where J11 = most recent data point


[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]AE10 = SUM(J2:J9)/8 (the simple Moving Average of the J col data points) [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]It IS doing (some) kind of exponential craziness, but clearly not what we are after.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Have you a thought on this, please?[/FONT]

 

[AlphaFrog]

You're out of my bailiwick on this one. Probably best to start a new thread. I'm sure somebody will jump in.

What's with the funky fonts and erratic line spacing? Pro tip: it doesn't help make your point.