B
Bob Vance
--
If B2 >0 "OVERDUE" <0 "CREDIT" But If B1 = B3 " VERY OVERDUE"
Thanks in advance for your help....Bob Vance
If B2 >0 "OVERDUE" <0 "CREDIT" But If B1 = B3 " VERY OVERDUE"
Thanks in advance for your help....Bob Vance
F
Frank Kabel
Hi Bob
=IF(B1=B3,"Very overdue",IF(B2>0,"Overdue",IF(B2<0,"Credit","B2 is
zero")))
Frank
Bob Vance wrote:
If B2 >0 "OVERDUE" <0 "CREDIT" But If B1 = B3 " VERY OVERDUE
=IF(B1=B3,"Very overdue",IF(B2>0,"Overdue",IF(B2<0,"Credit","B2 is
zero")))
Frank
Bob Vance wrote:
If B2 >0 "OVERDUE" <0 "CREDIT" But If B1 = B3 " VERY OVERDUE
K
Ken Wright
I'm assuming there is some dependency between B1, B2 and B3 as the logic was
kind of fuzzy. Also what if B2=0, or is that not possible in your data??
=IF(B1=B3,"VERY OVERDUE",IF(B2>0,"OVERDUE","CREDIT"))
kind of fuzzy. Also what if B2=0, or is that not possible in your data??
=IF(B1=B3,"VERY OVERDUE",IF(B2>0,"OVERDUE","CREDIT"))
N
Norman Harker
Hi Bob!
I think that the following covers most realistic possibilities:
=IF(B2<0,"CREDIT",IF(B2=0,"",IF(B1=B3,IF(B1<=0,"OVERDUE","VERY
OVERDUE"))))
But there are options not covered.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
I think that the following covers most realistic possibilities:
=IF(B2<0,"CREDIT",IF(B2=0,"",IF(B1=B3,IF(B1<=0,"OVERDUE","VERY
OVERDUE"))))
But there are options not covered.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob Vance
Sorry yes you are Wright 
Can you make it then if B1 >0 " very overdue" remembering B1 also has a
formula in it,So if B1 is zero then go on to this formula B2 <0 "credit" >0
"overdue". I hope this makes sense!
-- Thanks in advance for your help....Bob Vance
Can you make it then if B1 >0 " very overdue" remembering B1 also has a
formula in it,So if B1 is zero then go on to this formula B2 <0 "credit" >0
"overdue". I hope this makes sense!
-- Thanks in advance for your help....Bob Vance
B
Bob Vance
Norman I am getting a FALSE on "Account Overdue"
And a "Very Overdue" showing all the time when both are zero!
-- Thanks in advance for your help....Bob Vance
And a "Very Overdue" showing all the time when both are zero!
-- Thanks in advance for your help....Bob Vance
N
Norman Harker
Hi Bob!
B1:B3 all have 3 different possibilities (excluding text and blanks)
<0, 0 and >0
And then there is the possibility of B1=B3
You need to specify what you want for all these options.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B1:B3 all have 3 different possibilities (excluding text and blanks)
<0, 0 and >0
And then there is the possibility of B1=B3
You need to specify what you want for all these options.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob Vance
OK if B1>0 "very overdue"but if <0 then go to
B2 >0 "Overdue"<0 "Credit"
Does that make sense
B2 >0 "Overdue"<0 "Credit"
Does that make sense
F
Frank Kabel
Hi Bob
you still haven't defined what will happen if B1=0 or B2=0 but try
=IF(B1>0,"Very overdue",IF(B2>0,"Overdue",IF(B2<0,"Credit","B2 =
zero")))
One question: what happens with your B1=B3 condition?
HTH
Frank
you still haven't defined what will happen if B1=0 or B2=0 but try
=IF(B1>0,"Very overdue",IF(B2>0,"Overdue",IF(B2<0,"Credit","B2 =
zero")))
One question: what happens with your B1=B3 condition?
HTH
Frank
N
Norman Harker
Hi Frank!
I think Bob needs to re-state question with all permutations.
But probably best would be to have separate cells. One testing B2 and
the other testing B1=B3
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
I think Bob needs to re-state question with all permutations.
But probably best would be to have separate cells. One testing B2 and
the other testing B1=B3
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
F
Frank Kabel
Hi Norman,
I agree. Maybe Bob can explain the values in B1-B3 in more detail (waht
do they represent, etc.9
Frank
I agree. Maybe Bob can explain the values in B1-B3 in more detail (waht
do they represent, etc.9
Frank
B
Bob Vance
I've taken away the b3 to simplify it, Just need the formula to look at b2
first if >0 " Very Overdue" then if it is 0 to look at B2 >0 "overdue" <0
"Credit"
first if >0 " Very Overdue" then if it is 0 to look at B2 >0 "overdue" <0
"Credit"
N
Norman Harker
Hi Bob!
You're confused!
"if it is 0 to look at B2 >0"
If B2 is 0, how can it be greater than 0.
I suggest formula just looking at B2:
=IF(B2<0,"CREDIT",IF(B2=0,"","OVERDUE"))
That covers all except your "VERY OVERDUE" requirement.
Moving forwards. If an account cannot be VERY OVERDUE unless it is
OVERDUE, you might build the VERY OVERDUE option into the place taken
by OVERDUE above.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
You're confused!
"if it is 0 to look at B2 >0"
If B2 is 0, how can it be greater than 0.
I suggest formula just looking at B2:
=IF(B2<0,"CREDIT",IF(B2=0,"","OVERDUE"))
That covers all except your "VERY OVERDUE" requirement.
Moving forwards. If an account cannot be VERY OVERDUE unless it is
OVERDUE, you might build the VERY OVERDUE option into the place taken
by OVERDUE above.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob Vance
Thanks , Ok I have gone with that, Just one thing when the B2 is blank I am
still getting a Overdue, may be because B2 has a =G2 formula in it?
-- Thanks in advance for your help....Bob Vance
still getting a Overdue, may be because B2 has a =G2 formula in it?
-- Thanks in advance for your help....Bob Vance
N
Norman Harker
Hi Bob!
This should correct that one:
=IF(B2="","",IF(B2<0,"CREDIT",IF(B2=0,"","OVERDUE")))
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
This should correct that one:
=IF(B2="","",IF(B2<0,"CREDIT",IF(B2=0,"","OVERDUE")))
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob Vance
BRILLIANT thanks Norman worked great
Thanks in advance for your help....Bob Vance
worked greatThanks in advance for your help....Bob Vance
N
Norman Harker
Hi Bob!
We're getting there.
Now you have the standard formula OK, you can work on that VERY
OVERDUE question.
If it can't be VERY OVERDUE unless it's OVERDUE, it looks like just a
case of playing with the argument that returns OVERDUE.
From earlier it looks like:
=IF(B2="","",IF(B2<0,"CREDIT",IF(B2=0,"",IF(AND(B1=B3,B3<>""),"VERY
OVERDUE","OVERDUE"))))
But I'm curious! Can you describe what B1 and B3 are?
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
We're getting there.
Now you have the standard formula OK, you can work on that VERY
OVERDUE question.
If it can't be VERY OVERDUE unless it's OVERDUE, it looks like just a
case of playing with the argument that returns OVERDUE.
From earlier it looks like:
=IF(B2="","",IF(B2<0,"CREDIT",IF(B2=0,"",IF(AND(B1=B3,B3<>""),"VERY
OVERDUE","OVERDUE"))))
But I'm curious! Can you describe what B1 and B3 are?
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
D
Dana DeLouis
This is probably wrong, so I'll just throw this out...
=IF(B1>0,"vod",B2)
...and custom format with
"Overdue";"Credit";;"Very Overdue"
Again, just an idea. May not cover everything.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
<snip>
=IF(B1>0,"vod",B2)
...and custom format with
"Overdue";"Credit";;"Very Overdue"
Again, just an idea. May not cover everything.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
Bob Vance said:
<snip>
B
Bob Vance
B1 picks up the overdue from last month, but if B3 is the same amount
overdue it has gone back another month and if it is the same amount overdue
then it must be a least 2 months overdue "very overdue". I am using
different cells and very overdue is really " This account has been overdue
for 60 days!"
-- Thanks in advance for your help....Bob Vance
overdue it has gone back another month and if it is the same amount overdue
then it must be a least 2 months overdue "very overdue". I am using
different cells and very overdue is really " This account has been overdue
for 60 days!"
-- Thanks in advance for your help....Bob Vance
N
Norman Harker
Hi Bob!
This looks like it:
=IF(B2="","",IF(B2<0,"CREDIT",IF(B2=0,"",IF(AND(B1=B3,B3<>""),"Overdue
more than 1 month","Overdue 1 month"))))
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
This looks like it:
=IF(B2="","",IF(B2<0,"CREDIT",IF(B2=0,"",IF(AND(B1=B3,B3<>""),"Overdue
more than 1 month","Overdue 1 month"))))
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.