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.

If Statement based on blank fields
 

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" wrote in message
oups.com...
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.

If Statement based on blank fields
 

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" wrote in message
oups.com...
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.

If Statement based on blank fields
 

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