SMALL Function across multiple date ranges

[Kingsof82]

Hi,

I'm looking to find the second lowest date entered in ranges from multiple worksheets. I can return the second lowest from each range individually, e.g.

=SMALL(Observation!I16:BF16,2)

However, attempting to pull the second lowest from all ranges using the following returns a value error:

=SMALL((Probation!J13:U13, Appraisal!J13:O13,Supervision!I13:CB13,Observation!I13:BF13),2)

Any help is greatly appreciated, thanks

 

[Peter_SSs]

Could it be to do with the archaic version of excel I'm running?

I think that Excel 2016 should be fine but I don't have that version to test.

What happens if you download and open this version of your file that I have saved? What do you see in column H (formulas and cell values)?

Dropbox

www.dropbox.com

 

[Kingsof82]

When I download and open the file, column H appears to work fine but the entire function is within braces. If I then click into the formula bar, the braces disappear and the number error returns.

 

[Peter_SSs]

When I download and open the file, column H appears to work fine but the entire function is within braces. If I then click into the formula bar, the braces disappear and the number error returns.

OK, thanks. I didn't think that formula would require the braces, but I did not have your excel version to test. If you click in the formula bar or edit the formula in any way you need to confirm the formula with Ctrl+Shift+Enter, not just Enter. That action will cause Excel to insert the braces (typing them in manually will not work) and the formula should then work for you.

 

[Kingsof82]

It works! Thanks.

In order to then extend this to adjacent columns, moving from the second lowest in H, to the third lowest in I, then the fourth in J, etc, which elements of the formulae would I need to modify?

 

[Peter_SSs]

oving from the second lowest in H, to the third lowest in I, then the fourth in J, etc, which elements of the formulae would I need to modify

Just change the 2 near the end of the formula to 3 then 4 etc.
Don't forget to confirm with Ctrl+Shift+Enter each time

 

[Kingsof82]

That's a winner!

Thank you very much for your patience, have a great day!

 

[Peter_SSs]

You're welcome. Glad we got there in the end.