Excel question

Badly Shaved Monkey · 21st August 2010, 01:33 AM

I use spreadsheets as glorified calculators but don't really use them to full advantage. One thing I've struggled with is whether you can make the results of an arithmetic or logical function interact with the "meta" functions that are available. Can you, for instance, create an IF statement so that the result alters the size of that cell or another one, or its fill colour or font etc?

GrandMasterFox · 21st August 2010, 01:49 AM

Originally Posted by Badly Shaved Monkey

I use spreadsheets as glorified calculators but don't really use them to full advantage. One thing I've struggled with is whether you can make the results of an arithmetic or logical function interact with the "meta" functions that are available. Can you, for instance, create an IF statement so that the result alters the size of that cell or another one, or its fill colour or font etc?

What version of excel you're using?

There's an optional for conditional formating.
It's not in the formula, it's an external menu option (using excel 2003 here)
Essentially you can setup a condition like "if the value of this cell is greater than 3 than change its color to green, if not change its color to red"

bobhope2112 · 21st August 2010, 02:55 AM

The short answer is no. Cell formatting is not exposed to the formulaic part of Excel. Macros, on the other hand, can make all sorts of adjustments to a spreadsheet through an extensive object model. So, if you want to programmatically reformat a spreadsheet, or just some of its cells, that's entirely doable.

Conditional formatting has already been mentioned. It is a sort of a midpoint between the cloistered domain of the cellular formula, and the wide-open object model.

Maybe it would help if you would describe what you're trying to do?

Badly Shaved Monkey · 21st August 2010, 03:45 AM

Thanks for the replies. I'm away from home at the moment and can't remember my exact version. It's on a Mac, but I can't remember whether I got it with my 3yo machine or bought it as standalone software.

At it's simplest, I have long lists of stock codes and I'd like to apply a test to them then highlight cells that have yielded a certain result by filling the cell with a colour.

So, if it was possible within formulae what I'd want is the equivalent of =IF(A1>0,Fill Red,). Since I can't do it in formulae I'd want the simplest route to achieve the same task.

Thanks for helping.

Zep · 21st August 2010, 03:48 AM

Yep, can be done easily, as noted above. Look for Conditional Formatting.

Example: http://www.contextures.com/xlcondFormat01.html

varwoche · 21st August 2010, 01:04 PM

Originally Posted by bobhope2112

Conditional formatting has already been mentioned. It is a sort of a midpoint between the cloistered domain of the cellular formula, and the wide-open object model.

The condition can include a reference to a cell.

Badly Shaved Monkey · 25th August 2010, 12:00 AM

Back from hols now and able to look at "conditional formatting".

Many thanks, I think that does just what I need.

It is always said that as a last resort one should RTFM, but in the absence of TFM, I'm always grateful to JREF.

21st August 2010, 01:33 AM	#1
Badly Shaved Monkey Anti-homeopathy illuminati member Join Date: Feb 2004 Location: Outside a banana and far from a razor Posts: 5,262	Excel question I use spreadsheets as glorified calculators but don't really use them to full advantage. One thing I've struggled with is whether you can make the results of an arithmetic or logical function interact with the "meta" functions that are available. Can you, for instance, create an IF statement so that the result alters the size of that cell or another one, or its fill colour or font etc?
	__________________ "i'm frankly surprised homeopathy does as well as placebo" Anonymous homeopath. "Alas, to wear the mantle of Galileo it is not enough that you be persecuted by an unkind establishment; you must also be right." (Robert Park) Is the pen is mightier than the sword? Its effectiveness as a weapon is certainly enhanced if it is sharpened properly and poked in the eye of your opponent.

21st August 2010, 02:55 AM	#3
bobhope2112 Thinker Join Date: Jun 2007 Posts: 187	The short answer is no. Cell formatting is not exposed to the formulaic part of Excel. Macros, on the other hand, can make all sorts of adjustments to a spreadsheet through an extensive object model. So, if you want to programmatically reformat a spreadsheet, or just some of its cells, that's entirely doable. Conditional formatting has already been mentioned. It is a sort of a midpoint between the cloistered domain of the cellular formula, and the wide-open object model. Maybe it would help if you would describe what you're trying to do?
bobhope2112 Thinker Join Date: Jun 2007 Posts: 187

21st August 2010, 03:45 AM	#4
Badly Shaved Monkey Anti-homeopathy illuminati member Join Date: Feb 2004 Location: Outside a banana and far from a razor Posts: 5,262	Thanks for the replies. I'm away from home at the moment and can't remember my exact version. It's on a Mac, but I can't remember whether I got it with my 3yo machine or bought it as standalone software. At it's simplest, I have long lists of stock codes and I'd like to apply a test to them then highlight cells that have yielded a certain result by filling the cell with a colour. So, if it was possible within formulae what I'd want is the equivalent of =IF(A1>0,Fill Red,). Since I can't do it in formulae I'd want the simplest route to achieve the same task. Thanks for helping.
	__________________ "i'm frankly surprised homeopathy does as well as placebo" Anonymous homeopath. "Alas, to wear the mantle of Galileo it is not enough that you be persecuted by an unkind establishment; you must also be right." (Robert Park) Is the pen is mightier than the sword? Its effectiveness as a weapon is certainly enhanced if it is sharpened properly and poked in the eye of your opponent.

21st August 2010, 03:48 AM	#5
Zep Banned Join Date: Sep 2002 Posts: 26,985	Yep, can be done easily, as noted above. Look for Conditional Formatting. Example: http://www.contextures.com/xlcondFormat01.html
Zep Banned Join Date: Sep 2002 Posts: 26,985	Last edited by Zep; 21st August 2010 at 03:49 AM.

21st August 2010, 01:04 PM	#6
varwoche Philosopher Join Date: Feb 2004 Location: Puget Sound Posts: 7,233	Originally Posted by bobhope2112 Conditional formatting has already been mentioned. It is a sort of a midpoint between the cloistered domain of the cellular formula, and the wide-open object model. The condition can include a reference to a cell.
	__________________ To survive election season on a skeptics forum, one must understand Hymie-the-Robot (and/or Fat Jack)

21st August 2010, 01:49 AM	#2
GrandMasterFox Graduate Poster Join Date: Oct 2009 Posts: 1,315	Originally Posted by Badly Shaved Monkey I use spreadsheets as glorified calculators but don't really use them to full advantage. One thing I've struggled with is whether you can make the results of an arithmetic or logical function interact with the "meta" functions that are available. Can you, for instance, create an IF statement so that the result alters the size of that cell or another one, or its fill colour or font etc? What version of excel you're using? There's an optional for conditional formating. It's not in the formula, it's an external menu option (using excel 2003 here) Essentially you can setup a condition like "if the value of this cell is greater than 3 than change its color to green, if not change its color to red"

25th August 2010, 12:00 AM	#7
Badly Shaved Monkey Anti-homeopathy illuminati member Join Date: Feb 2004 Location: Outside a banana and far from a razor Posts: 5,262	Back from hols now and able to look at "conditional formatting". Many thanks, I think that does just what I need. It is always said that as a last resort one should RTFM, but in the absence of TFM, I'm always grateful to JREF.
	__________________ "i'm frankly surprised homeopathy does as well as placebo" Anonymous homeopath. "Alas, to wear the mantle of Galileo it is not enough that you be persecuted by an unkind establishment; you must also be right." (Robert Park) Is the pen is mightier than the sword? Its effectiveness as a weapon is certainly enhanced if it is sharpened properly and poked in the eye of your opponent.