Thanks:  0
Likes:  0

# Thread: Multiple results in the same cell

1. Yea I went back and did some testing...
Use my =OFFSET(F27,(COUNT(C1:C26)-27),0)
and don't have any zeros in row C below the current entry and things seem to work fine.
If you have zero's below C's today value remove them by unchecking viewing zero values in Tools...options.. view section of tool bar

2. On 2002-05-07 20:07, Vwman wrote:
Didn't work.

I will manually enter a number in C4. F4 is a couple of nested functions. Specifically:
=IF(C4>0,SUM(\$C\$3:\$C4)/B4*\$B\$27,0)
I want the result of this both in F4 and F27.
Then when I enter a number in C5, I want it displayed in F5 and F27.

Now, really, the only one that matters is F27. I just want it updated every time I add a number to the C column. In fact I would do better without the F3 to F25 cells but I need those formulas.

I'd be happy to email anyone a small sample of what I'm trying to do.

Thanks!
In F27 enter:

=IF(B4,SUM(OFFSET(C3,0,0,MATCH(9.99999999999999E+307,C:C)-2,1))/B4*B27,0)

3. Here is my answer that I have come up with thanks to your help.
F27 has the formula =OFFSET(F27,(COUNT(F3:F25)-25),0)
The cells F3:F:25 contain: =+IF(COUNT(C3:C3)=1,SUM(\$C\$3:\$C3)/B3*\$B\$27,"")

That seems to be accomplishing what I asked for. Using VB code is fine, but it is easier for me to understand and follow it through if I do it this way. The next time I need something like this I can adjust or rebuild from this one. With the VB code I'd never get the changes right, but I still learned from it.

Thank you both for the help!

Tom White
Vwman

4. On 2002-05-08 13:29, Vwman wrote:
Here is my answer that I have come up with thanks to your help.
F27 has the formula =OFFSET(F27,(COUNT(F3:F25)-25),0)
The cells F3:F:25 contain: =+IF(COUNT(C3:C3)=1,SUM(\$C\$3:\$C3)/B3*\$B\$27,"")

That seems to be accomplishing what I asked for. Using VB code is fine, but it is easier for me to understand and follow it through if I do it this way. The next time I need something like this I can adjust or rebuild from this one. With the VB code I'd never get the changes right, but I still learned from it.

Thank you both for the help!

Tom White
Vwman
Tom,

The MATCH construction might serve you better than COUNT in case you would have blanks in the range of interest.

Yours doesn't quite get it done. If I enter 10 in C3 the result in F27 is 230. Fine.
Then if I enter 11 in C4, the result in F27 is 483 when it should be 242.
Then if I enter 9 in C5, the result in F27 is 690 when it should be back to 230. Since I don't really need the F3:F25 (F27 is critical) yours might be the best solution if we can get it right.

Thanks for the help.

There are times I will need to enter a 0 in the C column. Yours does not give a new result if 0 is used. I could even have a negative number at times that needs to be calculated.

Thanks,

Tom

7. On 2002-05-08 15:11, Vwman wrote:

There are times I will need to enter a 0 in the C column. Yours does not give a new result if 0 is used. I could even have a negative number at times that needs to be calculated.

Thanks,

Tom
If you like, send me your WB; Or, post a small portion of your data in the follow up.

Postscript (after having seen your worksheet).

Given the following data area, occupying A1:C27, where "" stands for an empty cell,

{"","Work day","Hours";
"","","Billed";
"Wed",1,10;
"Thu",2,10;
"Fri",3,8.9;
"Mon",4,7.75;
"Tue",5,11.25;
"Wed",6,5.5;
"Thu",7,10.6;
"Fri",8,0;
"Mon",9,12.5;
"Tue",10,11.5;
"Wed",11,8.2;
"Thu",12,13.8;
"Fri",13,14;
"Mon",14,14.2;
"Tue",15,12;
"Wed",16,12;
"Thu",17,0;
"Fri",18,15;
"Mon",19,16;
"Tue",20,11.85;
"Wed",21,"";
"Thu",22,"";
"Fri",23,"";
"","","";
"# of days",23,205.05}

Given a goal (total hours), you want to track after each entry in C, so to speak, the distance to the goal.

You stated to prefer not to use an additional column for tracking. Given this preference:

In E1:H1 enter:

{"Goal","Days Worked","Current","Distance To Goal"}

In E2 enter: 230 [ the Goal hours ]

In F2 enter:

=IF(C3,MATCH(9.99999999999999E+307,\$C\$3:\$C\$25),0)

In G2 enter:

=IF(F2,SUM(OFFSET(C3,0,0,F2,1))/F2*B27,0)

In H2 enter:

=E2-G2

[ This Message was edited by: Aladin Akyurek on 2002-05-09 01:28 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•