Cell Reference: Based on Conditions... sort of

[santeria]

I am trying to sort out how a Cell link/reference would be if the Cell to be Displayed based on either a Greater than 0 reference, or something in the cell.
So if the cell link had a cell value greater than 0, or if it displayed anything, then display it, but if it displayed nothing, leave the cell blank.

The Cell links are in a Summary page, and they refer to cells that are refreshed by other macros.
So the Cell linked might be

'RAW SKILL 77'!I5

However, on a straight link, this has problems when trying to summarise, and calculate formalas etc.

To get around this, might the formula be:

=IF('RAW SKILL 77'!I5="",'RAW SKILL 77'!I5,"")

Would this take into account Just the cell with any content?
How would a Cell with 0 displayed in it be factored in?

Ta

 

[Zack Barresse]

are you looking to say if I5 is = 0, display nothing, but aslo if I5 is = "" display nothing?

if so, use an and statement:

=IF(AND('RAW SKILL 77'!I5="",'RAW SKILL 77'!I5=0),'RAW SKILL 77'!I5,"")

edit: excuse me, you would probably want to use an OR statement.

 

[santeria]

If I5 is 0, or has no text, then nothing is displayed, but if it has some value, or some text, number etc, then the cell is displayed.

Not clear on how I would put the OR statement in....

Ta

 

[Aladin Akyurek]

Not sure exactly what you are asking, but it seems you need something like:

=IF(N('RAW SKILL 77'!I5),'RAW SKILL 77'!I5,"")

which means: if I5 houses either a 0 or text like "" or I5 is empty, give "", otherwise give the non-zero number I5 houses.

 

[Zack Barresse]

=IF(OR('RAW SKILL 77'!I5="",'RAW SKILL 77'!I5=0),"",'RAW SKILL 77'!I5)

 

[santeria]

So Cool !!!!!

Both these Formula Work !!!
Thanks Aladin , and Thanks firefytr!!!

I guess I better test, but it seems like both are variations which produce the same end result ( I think).

Thanks for the effort, and thanks for the Result !!!!

Ta Muchly

ray: ray:

 

[santeria]

The Key Difference in the Formula is that Firefytr's Formula takes care of Seconds.

Both Work Great, just different strong points.

Cool.

Ta Muchly

ray: ray:

 

[santeria]

This is a non-mathematician type of query:

Since the Cell Reference formulas take out the Zeros, does that help in calculating Weighted Averages, standard Averages and such-like?

My presumption is that a bunch of Zeros is just as problematic as a bunch of high end numbers... or am I wrong in that assumption?

Ta

 

[Zack Barresse]

should, as though most average functions do not consider a "" (blank) cell into it's calculation event. nor should it affect stdev functions. to my knowledge that is.

edit: and yes, the zero's would be problematic!

 

[Aladin Akyurek]

...Both Work Great, just different strong points...

Strong points? They are designed for different inputs. Mine addresses, as the qualification I added specifies, non-zero numeric values. It looks like you have time values in text-formatted form. This creeps up every time you have a question. Why don't you work with data that is fully converted to true times values?