chevysoldier Posted November 28, 2011 Report Share Posted November 28, 2011 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? Quote Link to comment Share on other sites More sharing options...
flounder Posted November 28, 2011 Report Share Posted November 28, 2011 (edited) 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 Quote Link to comment Share on other sites More sharing options...
JStump Posted November 28, 2011 Report Share Posted November 28, 2011 What are you using this for? Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 28, 2011 Author Report Share Posted November 28, 2011 flounder said: 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. jstumpalump150 said: What are you using this for?Aquarium tracker. Quote Link to comment Share on other sites More sharing options...
JStump Posted November 28, 2011 Report Share Posted November 28, 2011 So you are manually putting these numbers in periodically? Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 28, 2011 Author Report Share Posted November 28, 2011 jstumpalump150 said: 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. Quote Link to comment Share on other sites More sharing options...
gen3flygirl Posted November 28, 2011 Report Share Posted November 28, 2011 I am telling you just get those red belly piranhas off Craig's list Quote Link to comment Share on other sites More sharing options...
jporter12 Posted November 29, 2011 Report Share Posted November 29, 2011 chevysoldier said: 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... Quote Link to comment Share on other sites More sharing options...
OsuMj Posted November 29, 2011 Report Share Posted November 29, 2011 (edited) something like this: =IF(OR(C7>C2:C6,C7<B2:B6),C7,"stop") didn't try it yet... Edited November 29, 2011 by OsuMj Quote Link to comment Share on other sites More sharing options...
Popular Post Disclaimer Posted November 29, 2011 Popular Post Report Share Posted November 29, 2011 =Dont((Trust),Mj,"She's a woman":WhichIs(< Men)) GOTO Kitchen 5 Quote Link to comment Share on other sites More sharing options...
ohdaho Posted November 29, 2011 Report Share Posted November 29, 2011 =IF(AND(C8<MIN(B2:B6), C8>MAX(C2:C6)),"Good","Bad") Quote Link to comment Share on other sites More sharing options...
jporter12 Posted November 29, 2011 Report Share Posted November 29, 2011 JRMMiii said: =Dont((Trust),Mj,"She's a woman":WhichIs(< Men)) GOTO Kitchen Quote Link to comment Share on other sites More sharing options...
jblosser Posted November 29, 2011 Report Share Posted November 29, 2011 (edited) ... Edited November 29, 2011 by jblosser Quote Link to comment Share on other sites More sharing options...
jblosser Posted November 29, 2011 Report Share Posted November 29, 2011 (edited) ... seems to not be working for me.***edit - didn't mean to double post Edited November 29, 2011 by jblosser Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 29, 2011 Author Report Share Posted November 29, 2011 gen3flygirl said: I am telling you just get those red belly piranhas off Craig's listlol, I don't want the other fish to get eaten. jporter12 said: 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. OsuMj said: something like this: =IF(OR(C7>C2:C6,C7<B2:B6),C7,"stop") didn't try it yet...I'll try it... JRMMiii said: =Dont((Trust),Mj,"She's a woman":WhichIs(< Men)) GOTO KitchenTried it. Kept changing the screen all white to match the rest of the appliances. ohdaho said: =IF(AND(C8<MIN(B2:B6), C8>MAX(C2:C6)),"Good","Bad")I'll try it. Quote Link to comment Share on other sites More sharing options...
OsuMj Posted November 29, 2011 Report Share Posted November 29, 2011 I meant c8 instead of c7. i still haven't tried it, but i already noticed it was wrong. Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 29, 2011 Author Report Share Posted November 29, 2011 OsuMj said: 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!" Quote Link to comment Share on other sites More sharing options...
ohdaho Posted November 29, 2011 Report Share Posted November 29, 2011 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. Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 29, 2011 Author Report Share Posted November 29, 2011 ohdaho said: 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. Quote Link to comment Share on other sites More sharing options...
OsuMj Posted November 29, 2011 Report Share Posted November 29, 2011 try this: =IF(OR(C8<MAX(B2:B7),C8>MIN(C2:C7)),"stop","good") 1 Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 29, 2011 Author Report Share Posted November 29, 2011 OsuMj said: 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. Quote Link to comment Share on other sites More sharing options...
OsuMj Posted November 29, 2011 Report Share Posted November 29, 2011 odaho had a good idea with the min/max commands. I don't think excel liked the array i was trying to give it. Quote Link to comment Share on other sites More sharing options...
jporter12 Posted November 29, 2011 Report Share Posted November 29, 2011 Now just go get some fish and see how they do. Quote Link to comment Share on other sites More sharing options...
OsuMj Posted November 29, 2011 Report Share Posted November 29, 2011 chevysoldier said: 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. Quote Link to comment Share on other sites More sharing options...
chevysoldier Posted November 29, 2011 Author Report Share Posted November 29, 2011 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 jporter12 said: 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. OsuMj said: 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... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.