Formula needed

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

--
If B2 >0 "OVERDUE" <0 "CREDIT" But If B1 = B3 " VERY OVERDUE"


Thanks in advance for your help....Bob Vance
 
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
 
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"))
 
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.
 
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
 
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
 
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.
 
OK if B1>0 "very overdue"but if <0 then go to
B2 >0 "Overdue"<0 "Credit"
Does that make sense
 
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
 
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.
 
Hi Norman,

I agree. Maybe Bob can explain the values in B1-B3 in more detail (waht
do they represent, etc.9
Frank
 
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"
 
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.
 
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
 
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.
 
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.
 
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
= = = = = = = = = = = = = = = = =


Bob Vance said:
OK if B1>0 "very overdue"but if <0 then go to
B2 >0 "Overdue"<0 "Credit"
Does that make sense
Click to expand...

<snip>
 
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
 
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.
 
Back
Top