Getting multiple IF statements to work

[Psycho0426]

I'm trying to automate a few features on our billing invoices, and for
the life of me, I cannot get my formula to work out right. I'm trying
to get a cell to look at the date in a certain cell and based on how
many days old it is, display one of four results (Current, 30+, 60+, &
90+). The formula I'm trying is below:

= IF (L16<=30),"Current",IF (L16<=60),"30+",,IF
(L16<=90),"60+",IF(L16>90),"90+"

To my eye, this looks correct, but somethings wrong.

 

[Dave Peterson]

Watch your parentheses:

=IF(L16<=30,"Current",IF(L16<=60,"30+",IF(L16<=90,"60+",IF(L16>90,"90+"))))

 

[Guest]

you also have two commas after 30+,, should only be one

--paul
(e-mail address removed)
remove nospam for email addy!

Watch your parentheses:

=IF(L16<=30,"Current",IF(L16<=60,"30+",IF(L16<=90,"60+",IF(L16>90,"90+"))))

 

[Psycho0426]

Ok, at least the formula gives a result now, but it's not figuring
correct. I attached to this message a zip of the invoice I'm working
on. Help me please, before I pull all my hair out...


+-------------------------------------------------------------------+
|Filename: AR-Blank.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5179 |
+-------------------------------------------------------------------+

 

[Dave Peterson]

Lots of people, including me, don't connect through excelforum.

And lots of people, including me, wouldn't open the file anyway.

If you don't get a response, you may want to post your problem in plain text.

 

[pikapika13]

Not that we dont' trust you..but no one opens files...
What is in L16? A date?

Then use this:
=IF(TODAY()-L16<=30,"Current",IF(TODAY()-L16<=60,"30+",IF(TODAY()-L16<=90
"60+",IF(TODAY()-L16>90,"90+")))

 

[Psycho0426]

Sorry about that, I didn't even think about that. I'll try to explain
exactly what's going on in this e-mail. First off, the first cell
should have been E16, not L16, as I previously stated. I had several
revisions & glanced at the wrong one. Second, E16 through E32 simply
look at the "D" column on the same row, and display how old it is in
one of four responses. The responses are Current (Current), more than
30 days oldbut less than 60 (30+), more than 60 days old but less than
90 (60+), more than 90 days (90+).

The current formula that I have in E16 is below:

=IF(D16<=30,"Current",IF(D16<=60,"30+",IF(D16<=90,
"60+",IF(D16>90,"90+"))))

The problem is, no matter what date appears to be displayed, the E
column is saying 90+. I'll give a couple of examples.


======================================================
| B | C | D |
======================================================
15 | Checkin | Checkout | Age |
======================================================
16 | 11/29/05 | 11/30/05 | 90+ |
======================================================
17 | 06/07/06 | 06/08/06 | 90+ |
======================================================

I hope this diplayed ok. As you can see, D16 is displaying right, but
D17 should read 30+, not 90+. Any ideas???

 

[Dave Peterson]

I'm betting that the value you have in D16 is not really a number--it's text.

If you've typed that value into D16, try this:
format the cell as General
then select the cell and hit F2 followed by enter.

Excel will see it as a number.

If you have lots of these "Text numbers" to convert,
select an empty cell
edit|copy
select the range to fix (all of column D???)
edit|Paste special|check Addition

==========
If you have a formula in D16, then make sure you're returning a number--not
text.

=if(a1="something","16","not 16")
will return the Text "16" w/o the quotes.

Use something like:
=if(a1="something",16,"not 16")

 

[Psycho0426]

D16 is actually a date in the format "08/13/2006" without the quotes, of
course. If I change D16 to general, won't the cell display that number
instead of the date it represents?

 

[Dave Peterson]

Select that D16 cell.
And format it to General.

If you still see 08/13/2006, then the value in that cell is really text--not
actually a date.

Format the cell as General
Retype the date
and excel should see your entry as a date.

(Try reformatting it to check--and then format it back to a date.)

If you have lots to do, you could try:
selecting the offending range
edit|Replace
what: /
with: /
replace all

And excel should reevaluate each entry as a date.

 

[carstowal]

I can understand why subtracting the dates is causing you so much
trouble.
Is there a cell you could create a formula in and hide it from
printing?
If so create a cell that is the invoice or statement date minus the
checkout date.
Format this as a number. We'll say this cell is E23.
Use the following formula:
=IF(E23<=30,"Current",IF(E23<=60,30,IF(E23<=90,60,IF(E23>90,"90+"))))

 

[Psycho0426]

Ok Carstowal, I did as you suggested. In L16 I put =TODAY()-D16 as
number then used the following in E16:

=IF(D16<=30,"Current",IF(D16<=60,"30+",IF(D16<=90
"60+",IF(D16>90,"90+"))))

Every cell in the E column regardless of date is now displaying 90+.
Any ideas?

EDI
Thank you Dave, I just saw your suggestion and will be trying it. I'l
get back here with my results, ok

 

[carstowal]

I don't understand why you put D16 in your formula.
The hidden cell you created shows the actual number of days late.
It seems that this is cell L16. (today minus the old date)
If so the D16 in your formula should be L16.

 

[Psycho0426]

Carstowal, good catch! I don't know what I was thinking. I guess I
should stay away from the keyboard when I'm tired. Now, it's
working. Thank you very much...

 

[Psycho0426]

Ok, one last question, then I'll stop pestering everyone. Now that I
have a formula that is working, how do I get it to not display anything
if the cell in column "L" is blank? The final formula I have is below:

=IF(L16<=30,"Current",IF(L16<=60,"30+",IF(L16<=90,
"60+",IF(L16>90,"90+"))))

Thanks again folks...

 

[Tibbs]

Ok, one last question, then I'll stop pestering everyone. Now that I
have a formula that is working, how do I get it to not display anything
if the cell in column "L" is blank? The final formula I have is below:

=IF(L16<=30,"Current",IF(L16<=60,"30+",IF(L16<=90,
"60+",IF(L16>90,"90+"))))

Thanks again folks...

=if(isblank(L16),"",IF(L16<=30,"Current",IF(L16<=60,"30+",IF(L16<=90,
"60+",IF(L16>90,"90+")))))

Tested it and it doesn't appear to have too many nested IFs...

Chris