Variable range based on data from a cell

VisualRealism

New Member
Joined
Jul 16, 2018
Messages
1
Hi,
I'm asking for your help, I'll try to explain as detailed as possible what I'm in need of(and couldn't figure out a way to do it yet).
So, I have a table that contains a number of products, for each product the yield is introduced each week, I would like to achieve to get the top 4 worst performing products out of the table for a specified week(like selecting a week from somewhere and based on that data(the value of the week) it selects the 4 worst for that week and enters them in 4 different cells) and to show the name of the product above the value that it extracted from the table.
The part with getting the worst performing ones is easy with =SMALL(range:range,nr in top) but this way I have to change the range each week, for example I'm on column Y this week so the formula is =SMALL(Y76:Y96,1) for the worst one but next week it should be =SMALL(Z76:Z96,1). Is there a way to select a week and after that the formulas range changes automatically? Let me know if something is unclear or more info is needed.

Thanks a lot in advance
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,095,934
Messages
5,447,396
Members
405,450
Latest member
JamesLH

This Week's Hot Topics

Top