Cell Reference: Based on Conditions... sort of

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

(y)
 

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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Oct 7, 2003
Messages
1,844
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

(y)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

(y) :pray: :pray: (y)
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844

ADVERTISEMENT

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

(y) :pray: :pray: (y)
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

:unsure:
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top