Conditional formatting formula
 

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
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

Bump...

Anyone have any ideas?


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

Anyone having any luck with this one?


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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" wrote in
message ...

Anyone having any luck with this one?


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile:
http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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....


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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" wrote in
message ...

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....


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile:
http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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 prior
to protecting the sheet.

Thanks for your help! :)


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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" wrote in
message ...

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 prior
to protecting the sheet.

Thanks for your help! :)


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile:
http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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. :)


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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 FormatConditional Formatting
Condition 1
Formula is:

=AND(COUNT(C8:C300)=2,AVERAGE(C8:C300)<AVERAGE(OF FSET(C8,,,COUNT(C8:C300)-1)))

Set the font color to GREEN

Condition 2
Formula is:

=AND(COUNT(C8:C300)=2,AVERAGE(C8:C300)AVERAGE(OF FSET(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" wrote in
message ...

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. :)


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile:
http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

Biff,

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


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile: http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215

Conditional formatting formula
 

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

"S3NTYN3L" wrote in
message ...

Biff,

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


--
S3NTYN3L
------------------------------------------------------------------------
S3NTYN3L's Profile:
http://www.excelforum.com/member.php...o&userid=30247
View this thread: http://www.excelforum.com/showthread...hreadid=505215