Help creating continuously calculating and updating formula

[sjm182]

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.

[T. Valko]

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" <sjm...@discussions.microsoft.com> wrote in message
news:AC51207E-DD0F-48A7...@microsoft.com...

[Stefi]

=SUM(AI8:INDEX(AN8:AP8,1,AH8))
where AN8:AP8 is the range of extension columns (adjust to the real column
numbers, e.g. to AQ8 or AR8, etc.
AH8 contains the actual column number in the above range, e.g. 1 for AN, 2
for AO, 3 for AP. Replace it by the place where actual sum range is defined!

Regards,
Stefi

„sjm182” ezt írta:

[sjm182]

Wow, I am so sorry that I don't understand this... I've been trying so hard
to make sense of it (and here I thought I was pretty fluent with Excel...).
It just doesn't work for me. I'll just be honest and tell you that I don't
know what you mean by the "range of extension columns" - is that meant to
encompass all of the cells that are eventually going to be filled in? And I
have no idea what you mean by the "actual column number" or what the actual
sum range is. Perhaps it would help if I posted a formula using more basic
cells/column identifiers.
I am using this formula to keep a running total of hours:
=SUM(C4:F4)-SUM(C3:F3) where I want to add data to row 3 - it will move to
encompass G3, H3, etc.
Here is the formula I am using to continuously tally % fulfillment (average):
=SUM(C3:F3)/SUM(C4:F4) where I want to, as above, add data to row 3
I would like to be able to use a formula that does not require me to change
the F column in the actual formula to G, H, etc. and will continuously update
and give me a non-error answer.
I'm very sorry if your answer below already solved this, I just really don't
have any idea how to manipulate it to work!!
Thanks!

[sjm182]

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:

> .
>

[T. Valko]

>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" <sjm...@discussions.microsoft.com> wrote in message

news:8B8D6ABA-4466-4D48...@microsoft.com...

[sjm182]

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:

> .
>

[T. Valko]

>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" <sjm...@discussions.microsoft.com> wrote in message

news:9D2075E6-836A-406B...@microsoft.com...

[sjm182]

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:

> .
>

[T. Valko]

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" <sjm...@discussions.microsoft.com> wrote in message

news:56E48F86-F885-492A...@microsoft.com...

[sjm182]

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:

> .
>

[T. Valko]

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" <sjm...@discussions.microsoft.com> wrote in message

news:8570CF86-D2F8-4EC0...@microsoft.com...