software

Ok, I have a spreadsheet with this code in H10

=IF(H6=quot;---quot;,-20,IF(G6gt;H6,(G6-H6)*5,IF(G6lt;H6,(H6-G6)*10/-1,0)))

In cell
D6=150
E6=quot;---quot;
F6=quot;---quot;
G6=quot;---quot;
H6=145

What I want is for the formula to skip over the cells that have the quot;---quot; 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

try something like this to find the last entered NUMBER in row 3
=LOOKUP(9.99999999999999E 307,3:3)
--
Don Guillett
SalesAid Software

quot;Excel Dummyquot; gt; wrote in message
...
gt; Ok, I have a spreadsheet with this code in H10
gt;
gt; =IF(H6=quot;---quot;,-20,IF(G6gt;H6,(G6-H6)*5,IF(G6lt;H6,(H6-G6)*10/-1,0)))
gt;
gt; In cell
gt; D6=150
gt; E6=quot;---quot;
gt; F6=quot;---quot;
gt; G6=quot;---quot;
gt; H6=145
gt;
gt; What I want is for the formula to skip over the cells that have the quot;---quot;
gt; in
gt; them and then do the equation with the last number entered, in this case
gt; in
gt; cell D6 and H6.
gt;
gt; Is this possible? Any help would be appreciated. Thanks!
gt;
gt; Michelle
Where would I put this code? In row 3?
quot;Don Guillettquot; wrote:

gt; try something like this to find the last entered NUMBER in row 3
gt; =LOOKUP(9.99999999999999E 307,3:3)
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Excel Dummyquot; gt; wrote in message
gt; ...
gt; gt; Ok, I have a spreadsheet with this code in H10
gt; gt;
gt; gt; =IF(H6=quot;---quot;,-20,IF(G6gt;H6,(G6-H6)*5,IF(G6lt;H6,(H6-G6)*10/-1,0)))
gt; gt;
gt; gt; In cell
gt; gt; D6=150
gt; gt; E6=quot;---quot;
gt; gt; F6=quot;---quot;
gt; gt; G6=quot;---quot;
gt; gt; H6=145
gt; gt;
gt; gt; What I want is for the formula to skip over the cells that have the quot;---quot;
gt; gt; in
gt; gt; them and then do the equation with the last number entered, in this case
gt; gt; in
gt; gt; cell D6 and H6.
gt; gt;
gt; gt; Is this possible? Any help would be appreciated. Thanks!
gt; gt;
gt; gt; Michelle
gt;
gt;
gt;

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.

--
Don Guillett
SalesAid Software

quot;Excel Dummyquot; gt; wrote in message
...
gt; Where would I put this code? In row 3?
gt; quot;Don Guillettquot; wrote:
gt;
gt;gt; try something like this to find the last entered NUMBER in row 3
gt;gt; =LOOKUP(9.99999999999999E 307,3:3)
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Excel Dummyquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Ok, I have a spreadsheet with this code in H10
gt;gt; gt;
gt;gt; gt; =IF(H6=quot;---quot;,-20,IF(G6gt;H6,(G6-H6)*5,IF(G6lt;H6,(H6-G6)*10/-1,0)))
gt;gt; gt;
gt;gt; gt; In cell
gt;gt; gt; D6=150
gt;gt; gt; E6=quot;---quot;
gt;gt; gt; F6=quot;---quot;
gt;gt; gt; G6=quot;---quot;
gt;gt; gt; H6=145
gt;gt; gt;
gt;gt; gt; What I want is for the formula to skip over the cells that have the
gt;gt; gt; quot;---quot;
gt;gt; gt; in
gt;gt; gt; them and then do the equation with the last number entered, in this
gt;gt; gt; case
gt;gt; gt; in
gt;gt; gt; cell D6 and H6.
gt;gt; gt;
gt;gt; gt; Is this possible? Any help would be appreciated. Thanks!
gt;gt; gt;
gt;gt; gt; Michelle
gt;gt;
gt;gt;
gt;gt;
Michelle,

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

gt;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=quot;quot;,quot;quot;,IF(D6=quot;---quot;,-20,IF(Lookup(999,$C6:C6)gt;D6,(Lookup(999,$C6:C6)-D6)*5,IF(Lookup(999,$C6:C6)lt;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 quot;---quot; entries.
The cells from D74 to T74 can then be copied down to as many rows as
you have people.

Hope this helps.

Pete