Need help making a formula for Excel

[chevysoldier]

First off, here is the basic chart.

I want a formula that will check to see if the average (blue) is less than any value in the "min" column or greater than any value in the "max" column. If either of these is true, I'd like the red box ( I only made it red to explain) to say something like "STOP" and change color. Hope that makes sense... Any idea how to do this?

[flounder]

had it but accidentally deleted it when I closed excel. Im sure someone will have it before I have time to gothrough it again.

Edited November 28, 2011 by flounder

[JStump]

What are you using this for?

[chevysoldier]

had it but accidentally deleted it when I closed excel. Im sure someone will have it before I have time to gothrough it again.

lol, that sucks.

What are you using this for?

Aquarium tracker.

[JStump]

So you are manually putting these numbers in periodically?

[chevysoldier]

So you are manually putting these numbers in periodically?

Yeah. Basically it's to track fish breed requirements. Using this spreadsheet, I can be sure I stock fish accordingly. I noticed today that the average number provided for something, might be out of the range for a certain fish and I might not catch it. I'm in the process of starting a new tank, the one from crb, and this makes it easier to decide what to stock.

[gen3flygirl]

I am telling you just get those red belly piranhas off Craig's list

[jporter12]

Yeah. Basically it's to track fish breed requirements. Using this spreadsheet, I can be sure I stock fish accordingly. I noticed today that the average number provided for something, might be out of the range for a certain fish and I might not catch it. I'm in the process of starting a new tank, the one from crb, and this makes it easier to decide what to stock.

You just might be over-doing it...

[OsuMj]

something like this: =IF(OR(C7>C2:C6,C7<B2:B6),C7,"stop") didn't try it yet...

Edited November 29, 2011 by OsuMj

[Disclaimer]

=Dont((Trust),Mj,"She's a woman":WhichIs(< Men)) GOTO Kitchen

[ohdaho]

=IF(AND(C8<MIN(B2:B6), C8>MAX(C2:C6)),"Good","Bad")

[jporter12]

=Dont((Trust),Mj,"She's a woman":WhichIs(< Men)) GOTO Kitchen

[jblosser]

...

Edited November 29, 2011 by jblosser

[jblosser]

... seems to not be working for me.

***edit - didn't mean to double post

Edited November 29, 2011 by jblosser

[chevysoldier]

I am telling you just get those red belly piranhas off Craig's list

lol, I don't want the other fish to get eaten.

You just might be over-doing it...

Maybe, maybe not. I do know I don't trust anyone at the fish store to know anything. They told me Tiger Barbs were peaceful community fish. Then they killed my Cories. So I like researching my own fish and knowing what I am getting into.

something like this: =IF(OR(C7>C2:C6,C7<B2:B6),C7,"stop") didn't try it yet...

I'll try it...

=Dont((Trust),Mj,"She's a woman":WhichIs(< Men)) GOTO Kitchen

Tried it. Kept changing the screen all white to match the rest of the appliances.

=IF(AND(C8<MIN(B2:B6), C8>MAX(C2:C6)),"Good","Bad")

I'll try it.

[OsuMj]

I meant c8 instead of c7. i still haven't tried it, but i already noticed it was wrong.

[chevysoldier]

I meant c8 instead of c7. i still haven't tried it, but i already noticed it was wrong.

So I just select the box and paste that into the text bar at the top? When I do that all I get is "#VALUE!"

[ohdaho]

I meant to switch the signs around...

=IF(AND(C8>MIN(B2:B6), C8<MAX(C2:C6)),"Good","Bad")

Means if the avg is greater than the mins and less than the max then youre ok. If not (one of the conditions is not met), then its Bad and some action is needed.

[chevysoldier]

I meant to switch the signs around...

=IF(AND(C8>MIN(B2:B6), C8<MAX(C2:C6)),"Good","Bad")

Means if the avg is greater than the mins and less than the max then youre ok. If not (one of the conditions is not met), then its Bad and some action is needed.

I get "good" no matter what.

[OsuMj]

try this: =IF(OR(C8<MAX(B2:B7),C8>MIN(C2:C7)),"stop","good")

[chevysoldier]

try this: =IF(OR(C8<MAX(B2:B7),C8>MIN(C2:C7)),"stop","good")

That worked! That's Mj! I never would have gotten that.

[OsuMj]

odaho had a good idea with the min/max commands. I don't think excel liked the array i was trying to give it.

[jporter12]

Now just go get some fish and see how they do.

[OsuMj]

I do know I don't trust anyone at the fish store to know anything. They told me Tiger Barbs were peaceful community fish. Then they killed my Cories. So I like researching my own fish and knowing what I am getting into.

me neither... they seem knowledgeable, but they are about 50/50 on whether they're right or not.

[chevysoldier]

The formula works as designed but I see a flaw. Notice the 30's. 30 is common among all the ranges. Say those were temperatures. You could set it at 30 and be okay among all the fish. See what I mean? My head hurts...I'm not used to doing stuff like this. lol

Now just go get some fish and see how they do.

I went and checked out a bunch today. But I need to get sand, live plants and rocks first.

me neither... they seem knowledgeable, but they are about 50/50 on whether they're right or not.

That's probably about right, 50/50...