Conditional Formatting with Formulas

K

kraljb

In my spreadsheet I have a months worth of sales hidden and then a plac
for people to enter the start date and end date and see the informatio
by day for that information. However, I need to make the Total an
Average rows bold and italized respectivly without using vba.

The hidden data is $b$8:$r$38
The end date chosen is $a$4

In column B (where the data starts) the formula is:

=IF(B41=$A$4,"Total",IF(B41="Total","Average",IF(OR(B41="average",B41=""),"",B41+1)))

Then in column C the formula is:

=IF($B42="","",IF($B42="Average",AVERAGE(C$40:C40),IF($B42="Total",SUM(C$40:C41),VLOOKUP($B42,$B$8:$R$38,2,FALSE))))

I can use conditional formatting to make the actual words "Total" an
"Average" become bold and italicized respectivly, but I cannot figur
out how to make columns C-R become italized. I tried to use th
"Formula Is" and then put in =AVERAGE(*) seeing as how what it i
calculating the average of varys on which cell, but it tells me it i
invalid.

Is there anyway to mass use a conditional format so that if it is usin
the SUM() part of the IF statement that I can have it be bolded, but i
it is using the AVERAGE() part it is italicized?

I cannot use VBA coding on this spreadsheet, so please do not offer
way to it with that.

Thank you in advanc
 
B

Biff

Hi kraljb!

Here's what you need to do:

Use the cell that holds the formula to base the CF on. You
also need to define what the average or sum is.

For example, if your formula is in cell A1 and the formula
returns either AVERAGE(C$40:C40) or SUM(C$40:C41), the CF
formula to use would be either:

=A1=AVERAGE(C$40:C40)
=A1=SUM(C$40:C41)

So, in this case do this:

Select the range that you want formatted.
Goto Format>Conditional Formatting
Formula is: =A1=AVERAGE(C$40:C40)
Format : italics
OK
Select the Add>> button
Condition 2
Formula is: =A1=SUM(C$40:C41)
Format: bold
OK out

Biff
 
B

Biff

P.S. -

This looks like a typo : AVERAGE(C$40:C40) but I just
copied it from your formula.

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top