Hello,
Can anyone help me to fix the following formula? I keep getting the #VALUE error. It works if I do the the C1:C4 portion in long form (C1=False,C2=False, etc.) but I'm looking for a faster way.
Entered in C5
Thanking you in advance for any and all help.
Please Login or Register to view this content.
Last edited by artiststevens; 03-02-2012 at 06:14 PM.
Excel probably won't understand what do you mean by C1:c4=False. For the AND function it should look like this. And(A5>=B5, C1:C4 = False). So the overall syntax should look like IF(And(A5>=B5, C1:C4=False),TRUE, False)
Hey JieJenn,
Thanks for the help but I'm still getting the #VALUE! error. Do you have any other ideas? Thanks again.
I tried.
Please Login or Register to view this content.
Double post.
Hi artiststevens,
If all of the values from C1 to C4 must be False, use below formula:-
=IF(AND(A5>=B5, C1=FALSE,C2=FALSE,C3=FALSE,C4=FALSE),TRUE, FALSE)
else, use below:-
=IF(AND(A5>=B5, OR(C1=FALSE,C2=FALSE,C3=FALSE,C4=FALSE)),TRUE, FALSE)
FALSE or TRUE may be changed to "False" and "True" for testing further
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Hi DILIPandey,Originally Posted by dilipandey
Hi artiststevens,
If all of the values from C1 to C4 must be False, use below formula:-
=IF(AND(A5>=B5, C1=FALSE,C2=FALSE,C3=FALSE,C4=FALSE),TRUE, FALSE)
else, use below:-
=IF(AND(A5>=B5, OR(C1=FALSE,C2=FALSE,C3=FALSE,C4=FALSE)),TRUE, FALSE)
FALSE or TRUE may be changed to "False" and "True" for testing further
Regards,
DILIPandey
<click on below 'star' if this helps>
Is there a faster way of doing it, because it would have to go to C50. Thanks for the help.
double post on my answer.
Last edited by vlady; 03-01-2012 at 02:15 AM. Reason: double post also
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
hello
can you try this one..
all cells C1:C5 should be FASLE if there is any other values will display aaa
=IF(AND(A5>=B5,CELL("contents",C1:C5)=FALSE),"fff","aaa")
yes.. there is a faster way but you need to confirm if all of the cells from c1 to c50 are supposed to be False or any one / few of them.
Also, tell me if False is appearing as FALSE or False or false in your spreadsheet?
Regards,
DILIPandey
<click on below 'star' if this helps>
Thanks Vlady that did it! I really appreciate the help from all of you!
Last edited by artiststevens; 03-01-2012 at 02:19 AM.
I wanted to thank everyone who helped me with fixing my formula, unfortunately upon further review the formula I thought worked had some flaws, so I once again ask for help with this matter.
I have attached a spreadsheet to give a better example of what I'm looking for. Thanking you again in advance for any and all help.
So are you saying 0.8960 is GREATER or EQUAL to 0.8967? I don't get the logic or your problem description.
Hi stevens,
For cell 28, in your example, you are saying that it should be False instead of True because C28 is greater than D28 and column E has at least one TRUE ?
Basically, as asked before in my post, I need to understand the logic for column E as :-
1) Any one cell on the above side should be True
or
2) All of the cell on the above side should be True
or what?
And
Cell in C must be greater than cell in D column.
Please clarify and this would be done easily. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Hi DiliPandey,
Cell 28 should be False because cell 24 is True. I'm looking for the first cell of the day in column C that is greater than or equal to the Value "A" in column D.
Since cell E24 is the first True instance all other cells after are False, until the next day.
Column E is looking for the first cell in column C that is greater than or equal to the adjacent cell in column D.
I hope that explains it better. I attached another spreadsheet breaking it down further.
Thanks again for the help.
Any further help? Thanks.
Hi
You have only a date in your Sheet(like your example), or many days?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi Fotis,
It would be for a whole. The example is just a small sample. From 14:00 to 14:00 is one day and each cell is a 15 minute interval. I hope that helps to explain it.
...Yes, helps to understand,but this.., is the problem...Or else...the solution is easy..From 14:00 to 14:00 is one day
I'll try..
What's the solution you have in mind? Thanks for trying.
Look...Stevens?
Point is that we need one true per day. I try to use COUNTIF, but i can not make it to work with TRUE or FALSE options..
If i make these TRU or FALS(or something else) works..
Then i modify a little, Vlandy's formula, and works.
But i can not fix it to "understand", when date changes in 16:00. I'll try some more...
Hey Fotis,
Thanks again. It doesn't have to reset at 16:00. If you can make it work for one day and I'll paste it all the way down. I've been trying with what you attached.
Hi
Yes, i can do this....Hey Fotis,
Thanks again. It doesn't have to reset at 16:00. If you can make it work for one day and I'll ........
Take a look to the example.
Hope to helps you.
That finally did it! Thank you Fotis!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks