What would you have done?

Recent question on AllExperts.com required me to come up with a solution to a question – which was, how to determine the MIDDLE year from a range where there are cars and colours quoted – so, put a car make and a colour in, then get the middle year. Took me less than 5 minutes to answer – and that’s because I learnt how to do array formulas some time ago! This needs one – the logic is – IF the car column matches the car and IF the colour matches the colour, the find the year – and THEN find the middle value – so how would YOU do this – well – with an array formula you can do it easily – and Array formula will allow you to use normal formulas to return multiple values and THEN use another formula on the result. My formula was =SMALL(IF(A1:A7=A13,IF(B1:B7=B13,C1:C7,999999),9999999),ROUND(SUMPRODUCT(–(A1:A7=A13),–(B1:B7=B13))/2,0))

in my example A13 contains the car to match and B13 the colour to match – this should be entered by pressing ctrl shift enter to turn it into an array formula. Do let me know if this helps you with YOUR formulas!

Leave a Reply