Getting multiple IF statements to work

**Psycho0426** · 08-10-2006, 09:27 PM

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 · 08-10-2006, 10:20 PM

Watch your parentheses:

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

Psycho0426 wrote:
>
> 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.

>
> --
> Psycho0426
>
> ------------------------------------------------------------------------
> Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832
> View this thread: http://www.excelforum.com/showthread...hreadid=570589

--

Dave Peterson

paul · 08-11-2006, 07:30 AM

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

--paul
paul.shepherd@nospamparadise.net.nz
remove nospam for email addy!

"Dave Peterson" wrote:

> Watch your parentheses:
>
> =IF(L16<=30,"Current",IF(L16<=60,"30+",IF(L16<=90,"60+",IF(L16>90,"90+"))))
>
>
>
> Psycho0426 wrote:
> >
> > 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.

> >
> > --
> > Psycho0426
> >
> > ------------------------------------------------------------------------
> > Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832
> > View this thread: http://www.excelforum.com/showthread...hreadid=570589
>
> --
>
> Dave Peterson
>

**Psycho0426** · 08-11-2006, 01:48 PM

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. The cells I'm trying to get to work are E16 through E32. Help me please, before I pull all my hair out...

Dave Peterson · 08-11-2006, 02:40 PM

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.

Psycho0426 wrote:
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> Psycho0426
>
> ------------------------------------------------------------------------
> Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832
> View this thread: http://www.excelforum.com/showthread...hreadid=570589

--

Dave Peterson

**pikapika13** · 08-11-2006, 03:28 PM

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** · 08-13-2006, 12:56 AM

Sorry about that, I didn't even think about that. I'll try to explain exactly what's going on in this post. 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, actually five. 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 fifth is it should be blank if there is no date at all in the "D" column.

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+. On lines with no date at all, Current is being displayed. 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 · 08-13-2006, 07:50 AM

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 wrote:
>
> 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???
>
> --
> Psycho0426
>
> ------------------------------------------------------------------------
> Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832
> View this thread: http://www.excelforum.com/showthread...hreadid=570589

--

Dave Peterson

**Psycho0426** · 08-14-2006, 01:44 AM

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 · 08-14-2006, 08:30 AM

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.

Psycho0426 wrote:
>
> 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?
>
> --
> Psycho0426
>
> ------------------------------------------------------------------------
> Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832
> View this thread: http://www.excelforum.com/showthread...hreadid=570589

--

Dave Peterson

**carsto** · 08-14-2006, 10:12 AM

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** · 08-15-2006, 12:46 AM

Ok Carstowal, I did as you suggested. In L16 I put =TODAY()-D16 as a 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?

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

**carsto** · 08-15-2006, 09:17 AM

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** · 08-17-2006, 09:05 PM

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** · 08-18-2006, 10:19 AM

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** · 08-18-2006, 11:31 AM

Originally Posted by 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...

=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

**cynthialam06** · 08-16-2010, 12:22 PM

IF(or(K2="AAP"),(K2),(k2="KG",(K2)),"")
I am trying to set a formula to enter a text based on if conditions. For example, if k2 is equal to aap, then enter aap or if k2 is equal to kg then enter kg if not then enter blank. Please help! Thanks