Returning Value within a cell with speicific criteria

RPopowich

Board Regular
Joined
Jan 6, 2005
Messages
89
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?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

RPopowich

Board Regular
Joined
Jan 6, 2005
Messages
89
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.
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Jul 17, 2004
Messages
2,838
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,342
Messages
5,624,114
Members
416,012
Latest member
rockermom59

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top