copying formula down

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BK - 26 Oct 2006 01:05 GMT

Using Excel 2003

Column A is dates. These dates plus 7 are being compared to the current
date to determine whether "overdue" gets displayed in Column D. I'm using
the following IF statement:

If A1+7<today(),"overdue"," "

When I copy the formula down, rows that have not yet had data entered into
them are all displaying "overdue" because Column A is blank. Do I just need
to remember to copy the formula down every time I enter a new row of data,
or is there some way I can modify my IF statement to only do the calculation
if there is data in Column A?

Reply to this Message

Max - 26 Oct 2006 03:17 GMT

Try instead in B1: =IF(A1="","",IF(A1+7<TODAY(),"overdue",""))
Copy down as far as required.
The additional front trap: =IF(A1="","",... will take care of the problem.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Using Excel 2003
>
[quoted text clipped - 9 lines]
> data, or is there some way I can modify my IF statement to only do the
> calculation if there is data in Column A?

Reply to this Message

BK - 26 Oct 2006 12:54 GMT

Of course!! I'm just getting used to nested IF statements, and I forgot all
about it.

Thanks!!

> Try instead in B1: =IF(A1="","",IF(A1+7<TODAY(),"overdue",""))
> Copy down as far as required.
[quoted text clipped - 12 lines]
>> row of data, or is there some way I can modify my IF statement to only do
>> the calculation if there is data in Column A?

Reply to this Message

Max - 27 Oct 2006 02:33 GMT

You're welcome !

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Of course!! I'm just getting used to nested IF statements, and I forgot
> all about it.
>
> Thanks!!

Reply to this Message

Otto Moehrbach - 26 Oct 2006 03:21 GMT

BK
Try this:
=IF(A1="","",IF(A1+7<TODAY(),"overdue","OK"))

HTH Otto

> Using Excel 2003
>
[quoted text clipped - 9 lines]
> data, or is there some way I can modify my IF statement to only do the
> calculation if there is data in Column A?

Reply to this Message

BK - 26 Oct 2006 12:55 GMT

Thanks so much for the reminder about nested IF statements!!

> BK
> Try this:
[quoted text clipped - 15 lines]
>> row of data, or is there some way I can modify my IF statement to only do
>> the calculation if there is data in Column A?

Reply to this Message

Ken Wright - 26 Oct 2006 12:37 GMT

If you are using 2003 then as long as you have in Tools / Options / Edit /
Checked the 'Extend data range and formulas' option, the formulas should
automatically be added when you put in data anyway, BUT, personally I like
belt and braces, so i'd still be there with Max and Otto :-)

Signature

Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)

> Using Excel 2003
>
[quoted text clipped - 9 lines]
> or is there some way I can modify my IF statement to only do the calculation
> if there is data in Column A?

Reply to this Message

BK - 26 Oct 2006 12:56 GMT

Didn't know about that option in 2003, but I've already changed to the
nested IF statements. Thanks so much for the information.

> If you are using 2003 then as long as you have in Tools / Options / Edit /
> Checked the 'Extend data range and formulas' option, the formulas should
[quoted text clipped - 19 lines]
>> calculation
>> if there is data in Column A?

Reply to this Message

Ken Wright - 30 Oct 2006 23:27 GMT

You're welcome :-)

> Didn't know about that option in 2003, but I've already changed to the
> nested IF statements. Thanks so much for the information.
[quoted text clipped - 24 lines]
>>> calculation
>>> if there is data in Column A?

Reply to this Message