How do I skip over null cells?

[Guest]

Ok, I have a spreadsheet with this code in H10

=IF(H6="---",-20,IF(G6>H6,(G6-H6)*5,IF(G6<H6,(H6-G6)*10/-1,0)))

In cell
D6=150
E6="---"
F6="---"
G6="---"
H6=145

What I want is for the formula to skip over the cells that have the "---" in
them and then do the equation with the last number entered, in this case in
cell D6 and H6.

Is this possible? Any help would be appreciated. Thanks!

Michelle

 

[Don Guillett]

try something like this to find the last entered NUMBER in row 3
=LOOKUP(9.99999999999999E+307,3:3)

 

[Guest]

Where would I put this code? In row 3?

try something like this to find the last entered NUMBER in row 3
=LOOKUP(9.99999999999999E+307,3:3)

 

[Don Guillett]

This is not code ( a macro is referred to as code). This is a formula. It
could go anywhere EXCEPT row 3.
It won't do all that you want but it will get you started in the right
direction as is will find the last NUMBER in row 3 which you can use as
desired.

 

[Pete]

Michelle,

I've tried emailing you several times, but it keeps bouncing back -
here's the gist of my reply:

From the description you supplied, I presume you are looking for a

formula which will compare weight loss or gain on a week-by-week basis
rather than comparing with the initial weight. I have assumed that if
the previous week's weight was missing, then you would want to compare
with the weight from the week before that (and so on until you find a
weight). If this is the case, then the following formula should be
entered or copied into cell D74:

=IF(D6="","",IF(D6="---",-20,IF(Lookup(999,$C6:C6)>D6,(Lookup(999,$C6:C6)-D6)*5,IF(Lookup(999,$C6:C6)<D6,(Lookup(999,$C6:C6)-D6)*10,0))))

This is all one formula, with no spaces. You might like to format the
cell to suit your requirements. This cell can be copied into the range
E74 to T74, and will give blanks rather than zeros if there is no entry
in the appropriate cell on row 6. The second C6 in the expression
$C6:C6 will change to D6, E6, F6 etc as you copy across the sheet. The
Lookup part of the formula will find the last numeric value in the
range (as suggested by Don above), and will ignore any "---" entries.
The cells from D74 to T74 can then be copied down to as many rows as
you have people.

Hope this helps.

Pete