Multiple results in the same cell

[Vwman]

I have a column with 23 cells. (One for each workday) Each day I will enter data that will give a result in that column.
For example F3 to F25. I will enter data in C3 and a result will appear in F3. Then data in C4, result in F4, etc.

I want the result in the F column to appear also in F27 with F27 changing to the new result each time I enter data in the C column.
If I enter data in C5 the result will appear in F5 and F27. When I enter data in C6 the result will appear in F6 but I want it
also in F27 instead of the F5 data. I have different formulas, with different results, that I want to display in the same cell. Each new
result takes priority over the previous result. Each new result could be higher or lower than the previous result. How do I do it?

I hope that makes sense.

 

[Nimrod]

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

 

[Aladin Akyurek]

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)

Aladin

 

[Vwman]

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

 

[Aladin Akyurek]

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.

Aladin

 

[Vwman]

Aladin,

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.

 

[Vwman]

Aladin,

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

 

[Aladin Akyurek]

On 2002-05-08 15:11, Vwman wrote:
Aladin,

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.

aladin_akyurek@yahoo.com


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}

Column C records hours made.

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