Returning Value within a cell with speicific criteria

[RPopowich]

Can you help me with a formula that would return the second to last that has data populated in the given range within a row? I will have 52 weeks of data going across. I want YTD value, 4 WK Avg value, 2WAGO value and current value. I have every column figured out except the 2 weeks ago heading. What formula can i use to return not the last cell populated in my range, but the next to last?

 

[Anne Troy]

How are you calculating 4 weeks ago?

 

[RPopowich]

I am doing a 4 Week AVG, not 4 WAGO. This is the formula:

=IF(COUNTA(B3:BA3)<4,AVERAGE(B3:BA3),AVERAGE(OFFSET($B3,,COUNTA(B3:BA3)-4,,4)))


I don't think I explained that right. Sorry. I need the 2 WAGO to show previous and current values.

 

[RPopowich]

This is still unanswered for me. Does anyone know how I can do this?

 

[PA HS Teacher]

Book1
	A	B	C	D	E
1
2	2	9	6	8	10
3
4	8
Sheet1

In the example above:
the next to last Value in row 2 is returned by:
=INDEX(A2:K2,MATCH(9.99*10^300,A2:K2)-1)

Change the range to meet your needs.

 

[Aladin Akyurek]

...
In the example above:
the next to last Value in row 2 is returned by:
=INDEX(A2:K2,MATCH(9.99*10^300,A2:K2)-1)

...

Are you trying to replace

9.99999999999999E+307

with

9.99*10^300?

I don't see the need, in particular when Excel's Help list specifically the first along with other significant constants:

Excel specifications and limits
[...]

Calculation specifications

Feature Maximum limit
Number precision 15 digits
Largest number allowed to be typed into a cell 9.99999999999999E+307
Largest allowed positive number 1.79769313486231E+308
Smallest allowed negative number -2.2251E-308
Smallest allowed positive number 2.229E-308
Largest allowed negative number -1E-307
Length of formula contents 1,024 characters
Iterations 32,767
Worksheet arrays Limited by available memory. Also, arrays cannot refer to entire columns. For example, an array cannot refer to the entire column C:C or to the range C1:C65536. However, an array can refer to the range C1:D65535 because the range is one row short of the maximum worksheet size and does not include the entire C or D column.
Selected ranges 2,048
Arguments in a function 30
Nested levels of functions 7
Number of available worksheet functions 329
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 9999:59:59

 

[Aladin Akyurek]

This is still unanswered for me. Does anyone know how I can do this?

It is not clear what you mean by last 2 or 4 values. If it's weeks 50 to 52 or 49 to 52, the "last entry" formulas would be misleading when values for these weeks are not yet available.

 

[PA HS Teacher]

Are you trying to replace

9.99999999999999E+307

with

9.99*10^300?

I don't see the need, in particular when Excel's Help list specifically the first along with other significant constants:

You are absolutely correct, it would be better to use 9.99999999999999E+307, it would make my use of this function consistent with other's use of it on this forum. I just didn't remember the exact limit offhand. Practically speaking you just need a really big number, so I picked a number in the ball park.

As for the 10^ notation, it is just more intuitive to me than E. I also insist my students use 10^Power notation, so I guess I feel an unconscious guilt about using E+Power.