# Returning Value within a cell with speicific criteria

#### RPopowich

##### Board Regular
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?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Anne Troy

##### MrExcel MVP
How are you calculating 4 weeks ago?

#### RPopowich

##### Board Regular
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

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

#### PA HS Teacher

##### Well-known Member

Book1
ABCDE
1
2296810
3
48
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.

##### MrExcel MVP
PA HS Teacher said:
...
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

##### MrExcel MVP
RPopowich said:
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

##### Well-known Member
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.

Replies
9
Views
233
Replies
0
Views
202
Replies
1
Views
177
Replies
4
Views
155
Replies
4
Views
343

### Forum statistics

1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back