If statement returning 0/01/1900
Hi all,
I have the following formula =IF(Main!T2<=$C$6,Main!T2,"0") If the date in a cell in the T column meets the criteria it returns that value, if it doesn't it returns 0, however if the cell is blank it returns 0/01/1900. How can I get it to just return a blank? Help please. hope this explanation makes sense.
hi there again Ronmac, maybe:
=IFERROR(1/(1/IF(Main!T2<=$C$6,Main!T2,"")),"")
could you list a few scenarios if i'm wrong? like:
when C6 is 30 Aug 13 & Main!T2 is 29 Aug 13 -> what should happen?
when C6 is 30 Aug 13 & Main!T2 is 30 Aug 13 -> what should happen?
when C6 is 30 Aug 13 & Main!T2 is 31 Aug 13 -> what should happen?
and so on...
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Try this...
Formula:
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Also, I have the following formula =IF(Main!T2<=$C$6,Main!E2,"FALSE") If the cell in Column T is blank it is returning a positive. I need it to return a negative. Problem is along the same lines. help please.
Hi Benishiryo, If C6 is 30 Aug 13 & Main!T2 is 29 Aug 13 -> should return T2
when C6 is 30 Aug 13 & Main!T2 is 30 Aug 13 should return T2
when C6 is 30 Aug 13 & Main!T2 is 31 Aug 13 should return 0
Hi
Sixthsense
Thanks Benishiryo, that worked. Cheers.
Thanks Benishiryo, that worked. Can you have a look at post 4 please. Cheers.
Hi Benishiryo, couldn't read your message.
Hi all,
I have generated a new spreadsheet which is attached. Column H1-H10 and J1-J10 is info I have
typed into the new spreadsheet.Column L1-10 and M 1-10 is data I have copied from my own spreadsheet.
As you can see the formula that is working under date 1 (kindly given to me by Benishiryo) relates to the new data. As is the formula under
User 1.
The formula under Date 2 relates to the copied data and returns incorrect data.It returns a correct
reading for cell 5 and an incorrect reading for cell 10.
The formula under Date 3 appears to be working ok on the copied data.
The formula under User 2 is relating to the copied data and is returning incorrect responses.
Hope all of this makes sense. I am starting to get a bit confused by it all. Help please.
So cell J18 also has an error but because there is something in L5 (even though it shows nothing). If you click in L5 and hit delete (even though there appears to be nothing in there) then cell J18 will also give a error result.
So under Date 2 (i.e. cell J14) put this formula and fill down: =IF(L1="","",IF(L1<=$C$6,L1,"FALSE"))
And under User 2 (i.e. cell N14) put this formula and fill down: =IF(L1="","",IF(L1<=$C$6,M1,"FALSE"))
TestBook problem.xlsx
Cheers
Hi Ursul, Thanks, I tried that and it seems to come up with a blank in the cell corresponding to L10? Cheers.Originally Posted by Ursul
You're welcome
in J14, try:
=IF(AND(L1<=$C$6,L1<>0),L1)
in N14:
=IF(AND(L1<=$C$6,L1<>0),M1)
at least, i think that's what you want. ideally, you should put in "Desired Results" in say K13 & type out manually the answers you are hoping to see in K14:K24 so we can compare it with J14:J24
Thanks Benishiryo,
That works, what do I need to change to return a blank when negative?
the IF formula has 3 parts.
1. the logical test
2. what to show/do when point 1 turns TRUE
3. what to show/do when point 1 turns FALSE
so if by saying "negative", you mean point 3 right? simply add in the 3rd point as "" to indicate blank
=IF(AND(L1<=$C$6,L1<>0),L1,"")
=IF(AND(L1<=$C$6,L1<>0),M1,"")
That worked, Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks