Conditional formatting formula

[S3NTYN3L]

OK, let me see if I can't explain what I'm trying to do without turning
your brain to swiss cheese. (Mine's already turned, lol)...

I'm working on a fuel log. (Check out the attachment)

In cells C5:H5 I've got formulas that average the data in their
respective columns.

In columns M thru V I've got formulas averaging things into two sets,
high and low. If any value is lower than the original average (row5) it
gets averaged into the "low" cell. same for "high"

Still with me?..

OK, you'll notice that cells C5:H5 have conditional formatting based on
the highs and lows.
If the "original" average (row5) is higher than the "high" average then
the text is green. If it's between the "high" and "low" then it's orange
and if it's lower than the "low" average it goes red...

I'm wanting to do away with the "high" and "low" cells and have the
conditional formatting formula take care of things.

Is this at all possible?

Let me know if you need me to make more sense of things...


+-------------------------------------------------------------------+
|Filename: MPG Explorer.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4268 |
+-------------------------------------------------------------------+

 

[S3NTYN3L]

Bump...

Anyone have any ideas

 

[S3NTYN3L]

Anyone having any luck with this one?

 

[Biff]

Hi!

I looked at your file. I think there is a flaw in your logic.

1. the avg will always be at least equal to or between the min avg and the
max avg.
2. the avg will never be higher than the max avg.
3. the avg will never be lower than the min avg.

Or am I not understanding what you want?

If the "original" average (row5) is higher than the "high" average then
the text is green. If it's between the "high" and "low" then it's orange
and if it's lower than the "low" average it goes red...

Biff

 

[S3NTYN3L]

I think you are correct now that I look at things...

I'm trying to have the averages in row 5 color coded so that it's green
if it's risen and red if fallen. (Reverse that in the case of the dollar
amounts on the left).

Something just now hit me...

Would this be a TREND function I'm thinking of?

I'm also going to have each entry color coded based on if the data
entered is higher or lower than the overall average.

In a nutshell, I'm wanting to be able to see how well my vehicle
mileage is and "guage" (when things start turning red) when it's time
to change the oil, air & fuel filters, give it a tune up, etc.

I'm also wanting to get rid of the cells on the right ( M-V I believe).
I don't like having to hide those columns...


I hope I haven't confused you even more....

 

[Biff]

Hi!

What you would have to do is compare the previous avg with the new avg. That
would require formulas to calculate both the previous avg and the current
avg. In other words, more helper cells which is apparently what you're
trying to eliminate!

I just did this exact same thing for someone who wanted to track their golf
score averages.

Biff

 

[S3NTYN3L]

What would that formula be?

I guess I can live with the helper cells.
I'll just but them all the way in down in columns IL thru IV just prio
to protecting the sheet.

Thanks for your help! :

 

[Biff]

Let me tinker around with your file then I'll post a link to it.

Seems to me that what you really need is a chart that will give you a visual
of the trend.

Biff

 

[S3NTYN3L]

Sounds good to me.

I was looking at turning it into a chart but I haven't been playing
with Excel all that long. Honestly, I just recently got comfortable
with formulas and functions.

I really like this probram but MS has strange ideas of what they think
is relevant results when your surfing an MS help file...

Thanks again for your help.

 

[Biff]

Here's the best I could come up with in regards to the conditional
formatting.

Using the Price Per Gallon category as an example. If the ppg is higher
that's no good and you probably want that colored red. If the ppg is lower
that's good and you probably want that colored green. If there is no change
then use the default color.

Of course, this only gives you the "trend" from the next to last entry to
the last entry. So, using ppg as the example:

Cell C5 = ppg average

Set the conditional formatting:

Select cell C5
Goto Format>Conditional Formatting
Condition 1
Formula is:

=AND(COUNT(C8:C300)>=2,AVERAGE(C8:C300)<AVERAGE(OFFSET(C8,,,COUNT(C8:C300)-1)))

Set the font color to GREEN

Condition 2
Formula is:

=AND(COUNT(C8:C300)>=2,AVERAGE(C8:C300)>AVERAGE(OFFSET(C8,,,COUNT(C8:C300)-1)))

Set the font color to RED

Since this is comparing the previous average to the current average there
will need to be at least 2 entries in the range. So, until you have 2
entries no formatting will be applied.

You can use this same logic and apply it to the other categories. This isn't
exactly what you had in mind which is why I think using charts will give you
the visual impact you're after.

Biff

 

[S3NTYN3L]

Biff,

How should I go about turning this into a chart that I can update a
new data is entered

 

[Biff]

Hi!

I almost forgot about this thread!

I hardly ever make charts myself, so I doubt that I can be of much help in
that regard.

This site:

http://www.peltiertech.com/

....is the best Excel chart resource on the net.

There's also a Charting forum group.

Biff