If Statement based on blank fields

I am trying to write an IF statement in a cell for a calculation based
on whether a cell is blank or not. This is what I have started with
but I know it isn't correct
=IF(ISBLANK(D7),(D6-D2))&IF(ISBLANK(D6),(D5-D2))

Example:

Cell
Weekly Standard 10,000 (D2)
Week 1 Input 2,000 (D3)
Week 2 Input 13,500 (D4)
Week 3 Input (D5)
Week 4 Input (D6)
Week 5 Input (D7)

Variance Amount (D9) (i.e D4-D2)

(This field should be based on the last entry made minus the weekly
standard.
The formula needs to start with Week #5 then 4 then 3 then 2. Any help
with this would be greatly appreciated especially with my limited
knowledge.

Giving overworked IF a rest, try
=D2-INDIRECT("D"&COUNT(D3:D7)+2)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"alaxmen" <[email protected]> wrote in message
news:[email protected]...
>I am trying to write an IF statement in a cell for a calculation based
> on whether a cell is blank or not. This is what I have started with
> but I know it isn't correct
> =IF(ISBLANK(D7),(D6-D2))&IF(ISBLANK(D6),(D5-D2))
>
> Example:
>
> Cell
> Weekly Standard 10,000 (D2)
> Week 1 Input 2,000 (D3)
> Week 2 Input 13,500 (D4)
> Week 3 Input (D5)
> Week 4 Input (D6)
> Week 5 Input (D7)
>
> Variance Amount (D9) (i.e D4-D2)
>
> (This field should be based on the last entry made minus the weekly
> standard.
> The formula needs to start with Week #5 then 4 then 3 then 2. Any help
> with this would be greatly appreciated especially with my limited
> knowledge.
>

As long as there are no gaps in the data, you could use

=OFFSET(D2,COUNTA(D2:D7)-1,0)-D2

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alaxmen" <[email protected]> wrote in message
news:[email protected]...
> I am trying to write an IF statement in a cell for a calculation based
> on whether a cell is blank or not. This is what I have started with
> but I know it isn't correct
> =IF(ISBLANK(D7),(D6-D2))&IF(ISBLANK(D6),(D5-D2))
>
> Example:
>
> Cell
> Weekly Standard 10,000 (D2)
> Week 1 Input 2,000 (D3)
> Week 2 Input 13,500 (D4)
> Week 3 Input (D5)
> Week 4 Input (D6)
> Week 5 Input (D7)
>
> Variance Amount (D9) (i.e D4-D2)
>
> (This field should be based on the last entry made minus the weekly
> standard.
> The formula needs to start with Week #5 then 4 then 3 then 2. Any help
> with this would be greatly appreciated especially with my limited
> knowledge.
>

Gentlemen, I tried both suggestions with equal results. Thanks for you
help in this matter for truly I could not done it with out you.
Thanks....