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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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.
 
Upvote 0

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)
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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.
 
Upvote 0

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
=IF(OR('RAW SKILL 77'!I5="",'RAW SKILL 77'!I5=0),"",'RAW SKILL 77'!I5)
 
Upvote 0

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)
 
Upvote 0

santeria

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

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:
 
Upvote 0

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!
 
Upvote 0

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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?
 
Upvote 0

Forum statistics

Threads
1,186,372
Messages
5,957,476
Members
438,307
Latest member
bigmike1720

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