conditional data?

[tpmbrian]

I have a table of data, weekly entries as follows

week 1 week 2 week 3 week 4 week 5 week 6 week 10 etc Plot

Availability 50 56 58 45 50 60

Performance 40 42 41 20 45 50

Yield 90 92 93 75 91 90 95

OEE 18.0 21.6 22.1 6.8 20.5 27.0 36.6


In the plot column I want to insert either the latest week, the average, or the best results based on a tick box or similar. Can this be done without me having to write VBA code, if so, how please?

 

[Hui]

Tmpbrian

Yes

You can use data validation for a drop down list and then a formula to get the answers

Have a look at an example with your data here:

https://rapidshare.com/files/461726005/tpmbrian.xlsx


For Row 2

Validation is in B2


The formula is :

=IF(B2="Latest Week",LOOKUP(9.999E+307,C2:L2),IF(B2="Average Week",AVERAGEA(C2:N2),MAX(C2:N2)))


Latest Week is LOOKUP(9.999E+307,C2:L2)

Average is AVERAGEA(C2:N2)

Best is MAX(C2:N2)