Condition Excel... And Repeat

May 7, 2012 2:38 PM

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)

I did not receive an error under the following circumstances:

-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

Best answer: You could try using the following vba function:

Function GetFormula(Cell as Range) as String
   GetFormula = Cell.Formula
End Function

Taken 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

OpenOffice has a FORMULA() function that does this, fwiw.
posted by fleacircus at 3:20 PM on May 7, 2012

Best answer: 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