Error in Formula

  • Thread starter Thread starter jctlcdc
  • Start date Start date
J

jctlcdc

=IF(AD2>=12,"PHII",IF(AD2>=24,"PHIII"))

In the formula above only the first half works. In other words, n
matter what the number in AD2 is, as long as its above 12, I get PHII.
I am trying to get PHIII if it is equal or greater than 24. What am
doing wrong here??
 
Hi jctlcdc!

If AD2>=12 also covers your cases where AD2>=24 so where AD2>=24 is
not covered by the false value.

Try:

=IF(AD2>=24,"PHIII",IF(AD2>=12,"PHII,"Less than 12"))

I've added a return for AD2<12

--
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.
 
If AD2 is above 24 it's above 12 as well so the first test is then TRUE en
thus your outcome is always PHII.

First test on AD2 above 24 and give it PHIII if true, else if above 12 PHII
and if below 12 ??? (PHI ??)

=IF(AD2>=24, "PHIII",IF(AD>=12,"PHII","Text if below 12"))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi.
Your formula is allowing the question to be correct for
both parts of the formula.
IF AD2>=12 can also be correct for IF AD2>=24 once it
reaches 25. What happens if the data is less than 12?
Try this:
=IF(AD2<12,"",IF(AD2<24,"PHII","PHIII"))

Regards
Michael
 
Peter,
Wow! Why didn't I think of that? Thanks for pointing that out.
have to start thinking like Excel, (or a Vulcan), I guess.
I changed to if AD2 is below 12 then PHI now. I just hadn't gotten tha
far.
Another question - I have another column that is "+ or -" which tell
me if a client is "0" (Even), "+" or "-" on their fee payments. I
they are "0" or >0 they can move to the next phase but if they ow
money even they have the required number of sessions they have to ge
caught up before they can move to the next phase. How could I make tha
another criteria? I tried using the AND function but the Excel hel
wasn't very helpful in showing me how to do it.
And then there will be one more criteria which would just be a chec
box - true or false
 
Hi jctlcdc!

The structure of AND conditions is e.g.

=IF(AND(AD2>=12,AE>=0,AF=TRUE),"Proceed","Don't Proceed")

--
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.
 
okay, so far the phI, phII and phIII formula is working fine but try a
I might I haven't been able to add the extra arguments of if ("+or -
column =0 or more $ to move to the next phase and I still need to and
true/false checkbox and make yes a criteria to mave to the next phas
as well. I am not understanding the "if and" function yet
 
Hi jctlcdc!

One way to test your conditions for an IF function is to type them in
a separate cell:

=AND($A$1=TRUE,$A$5="Yes",AE5=5)
Returns TRUE if A1 is TRUE, A5 is Yes and AE5=5. Otherwise it returns
FALSE.

The same technique can be used with OR.

Note the use of absolute and relative referencining. With absolute
referencing, when I copy down or across the formula will still look to
the target cells (A1 and A5). With relative referencing it will change
the cell looked at by one row for each row copied down and similarly
for columns.


--
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