Ok, if I understand, then you only want to use cells in both rows that have
data entered.

If this was your data:

...........C..........D
3........1.............
4........2...........1

Then D4 should be excluded from the calculation. Is that correct?

If so, try these:

Assuming the potential end of the range is column AA.

Formula for A2:

=SUM(C3:AA3)/SUMIF(C3:AA3,"<",C4:AA4)

Formula for B2:

=SUMIF(C3:AA3,"<",C4:AA4)-SUM(C3:AA3)

--
Biff
Microsoft Excel MVP


"sjm182" wrote in message
...
Here is a portion of my table:

1 A B C D E
F G H

2 58% 3.75

3 Completed 1 1 0 0.25 3
4 prescribed 2 2 2 1.5 1.5 1.5
5 50% 50% 0% 17% 200%
Cell A2 has the formula: =SUM(C3:G3)/SUM(C4:G4) with the goal of finding
the
% fulfillment of hours completed/prescribed (using only data inputted, in
this case up to column G). If I included the blank spaces as well (column
H), then the % fulfilled to date would be 50%, which is not accurate.
Cell B2 has the formula: =SUM(C4:G4)-SUM(C3:G3) with the goal of finding
the
# of hours left to fulfill (again using only data inputted). If I
included
column H, the total hours remaining would be 5.25, which again is not
accurate.
I'm trying to make a worksheet that employees can use to track this info
and
not have to continuously update the formula to extend to the next column.
Honestly, I'm thinking the easiest way to do it is just to input the row 3
and 4 info each week and have the formula extend to the end of the period.
It would be ideal if the prescribed hours could all be filled in advance,
though.

"T. Valko" wrote:

I'm pretty sure we can solve this.

Can you post a small snippet of data and tell me what result you expect.
Be
very specific as to where the data is located. The more details you
provide
the easier it is for us to understand what you're trying to do.

--
Biff
Microsoft Excel MVP


"sjm182" wrote in message
...
True, but if the bottom cells have data in them, it will divide the 2
sums
giving an inaccurate number based on the current data. I think I'm
asking
too much of Excel, but thanks for the attempts!

"T. Valko" wrote:

Does that make sense?

SUM ignores empty cells so how do the empty cells affect the formula?
If
you
have:

10...5...2...empty...empty...empty...empty to the end of the row

The sum of that row is 17 no matter how many empty cells there are
after
the
last entry.

--
Biff
Microsoft Excel MVP


"sjm182" wrote in message
...
I am figuring a running total where the bottom row is the required
hours
(known ahead of time) and the top row is actual hours (entered
daily).
If
I
include all of the cells in the range with a blank on the top row,
it
totals
the % hours fulfilled wrong because it looks as if all the blank
rows
are
zeros; I'm also figuring a running total of the difference between
the
two
totals as the hours are filled and this is also wrong if it counts
the
blanks. Does that make sense? I think I'm being quite difficult
without
meaning to be :-)

"T. Valko" wrote:

because there is data in the bottom row,
but not the top, the answer it returns is incorrect.

I don't understand how that could cause the formula to return an
incorrect
result?

--
Biff
Microsoft Excel MVP


"sjm182" wrote in message
...
Thanks, I tried that, but because there is data in the bottom
row,
but
not
the top, the answer it returns is incorrect. I dont' even know
if
what
I"m
asking is possible - I want to create a form that I can share
with
others
to
use, but I dont' want to have to instruct them to update the
formula
each
time you add in a new value.

"T. Valko" wrote:

If there's no other numerical data beyond the last entry to the
end
of
the
row just expand the range:

=SUM(AI49:IV49)-SUM(AI48:IV48)

--
Biff
Microsoft Excel MVP


"sjm182" wrote in message
...
I'm not sure if I'm asking this correctly, so please help me
clarify
if
needed!

first example:
=SUM(AI49:AN49)-SUM(AI48:AN48) in which "AN" is regularly
extended
(AO,
AP,
etc.).
How can I create a formula that gives me an answer when cell
49
is
standard
and cell 48 is continuously updating? Currently, I am
manually
changing
"AN"
to "AO", and so on as I update the information.

second example:
=SUM(AI48:AN48)/SUM(AI49:AN49) with the same situation as
above.




.



.



.



.