May 7, 2012 2:38 PM Subscribe

Excel Filter: Advanced Conditional Formating, Format based on type of formula used (average, sum, sumproduct etc..)

How do I conditionaly format a cell based on the formula. I would like to be able to search a cell for what type of functions are being used.

Say cell A1 = Average(A2:Z2)

and in A2 = if(iserror(find("Average",A1,1)),"NO","YES")

result of the find formula in this case is error, because it can't find the word average. I would like it to be able to search the formula and not the result.

how do I trick excel to let me search the formula for a specific keyword??? Is their a cross function to do this.

based on the formula in A2 you could easily bury that into a conditional formula assuming it cand find the term used in the function.
posted by MechEng to Computers & Internet (5 answers total) 3 users marked this as a favorite

How do I conditionaly format a cell based on the formula. I would like to be able to search a cell for what type of functions are being used.

Say cell A1 = Average(A2:Z2)

and in A2 = if(iserror(find("Average",A1,1)),"NO","YES")

result of the find formula in this case is error, because it can't find the word average. I would like it to be able to search the formula and not the result.

how do I trick excel to let me search the formula for a specific keyword??? Is their a cross function to do this.

based on the formula in A2 you could easily bury that into a conditional formula assuming it cand find the term used in the function.

You could try using the following vba function:

Then you could use this to extract the formula text into a cell and then run your conditional on the contents of that cell, or just bake the GetForumla function into your conditional.

posted by Reverend John at 3:08 PM on May 7, 2012

Function GetFormula(Cell as Range) as String GetFormula = Cell.Formula End FunctionTaken from dmcritchie.mvps.org/excel/formula.htm

Then you could use this to extract the formula text into a cell and then run your conditional on the contents of that cell, or just bake the GetForumla function into your conditional.

posted by Reverend John at 3:08 PM on May 7, 2012

BTW. You'll need to paste the above function into a new vba module in your spreadsheet in order to be able to use, just in case you're not familiar with custom functions.

The basics are covered here, or you could just google "custom excel function".

posted by Reverend John at 3:16 PM on May 7, 2012

The basics are covered here, or you could just google "custom excel function".

posted by Reverend John at 3:16 PM on May 7, 2012

OpenOffice has a FORMULA() function that does this, fwiw.

posted by fleacircus at 3:20 PM on May 7, 2012

posted by fleacircus at 3:20 PM on May 7, 2012

FlyingMonkey, if you plug in just the find portion of your formula what do you get? #name?

That would be an error, if you notice the if statement is doing error handling, therefore if their is an error it spit out No for it can't find it. Yes would be for it could find it. (because their should be a value between 1 and 255.

Thank you Reverend John for your little tid bit. Taken from dmcritchie.mvps.org/excel/formula.htm

I also use array formulas, so this variation of the formula is also helpful

Variations of the GetFormula User Defined Function:

The following variation might look better but would not match the Formula view of Excel.

Advantage is it shows a single quote if the cell shows up AS TEXT, and it shows array formulas as array formulas with the braces.

Function GetFormulaI(Cell as Range) as String

'Application.Volatile = True

If VarType(cell) = 8 And Not cell.HasFormula Then

GetFormulaI = "'" & cell.Formula

Else

GetFormulaI = cell.Formula

End If

If cell.HasArray Then _

GetFormulaI = "{" & cell.Formula & "}"

End Function

posted by MechEng at 7:42 AM on May 8, 2012

That would be an error, if you notice the if statement is doing error handling, therefore if their is an error it spit out No for it can't find it. Yes would be for it could find it. (because their should be a value between 1 and 255.

Thank you Reverend John for your little tid bit. Taken from dmcritchie.mvps.org/excel/formula.htm

I also use array formulas, so this variation of the formula is also helpful

Variations of the GetFormula User Defined Function:

The following variation might look better but would not match the Formula view of Excel.

Advantage is it shows a single quote if the cell shows up AS TEXT, and it shows array formulas as array formulas with the braces.

Function GetFormulaI(Cell as Range) as String

'Application.Volatile = True

If VarType(cell) = 8 And Not cell.HasFormula Then

GetFormulaI = "'" & cell.Formula

Else

GetFormulaI = cell.Formula

End If

If cell.HasArray Then _

GetFormulaI = "{" & cell.Formula & "}"

End Function

posted by MechEng at 7:42 AM on May 8, 2012

This thread is closed to new comments.

-in column A, enter random numbers in several rows

-in B1, enter =AVERAGE(A1:A

[last])-in C1, enter =IF(ISERROR(FIND(Average,B1,1)),"NO","YES")

it's finding the word Average fine in B1

(

but!when i use the insert-formula box, it adds quotation marks around Average, and then it returns an error message. So try getting rid of them--select the cell, hit [F2]*, and remove them by hand.* - windows. not sure about mac.)

posted by FlyingMonkey at 2:53 PM on May 7, 2012