# Cell Reference: Based on Conditions... sort of

#### santeria

##### Well-known Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Zack Barresse

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

##### MrExcel MVP

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

#### santeria

##### Well-known Member
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

##### Well-known Member

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

##### Well-known Member
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

##### MrExcel MVP
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!

##### MrExcel MVP
santeria said:
...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?

Replies
6
Views
277
Replies
7
Views
317
Replies
9
Views
2K
Replies
1
Views
351
Replies
1
Views
113

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,897
Messages
5,767,001
Members
425,393
Latest member
SushiJuice

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back